求一SQL语句~一直没搞明白！
比如说，我两张表
表A是一个企业的基本信息表
字段：
id cor_id cor_name
表B是企业发送消息的列表
id cor_id message
两张表用企业用户名相关联 ，cor-id相管理
我如何查询出最新15个发送消息的企业的名称呢？
[146 byte] By [msdn] at [2007-8-15 10:02:10] 
# 2 Re: 求一SQL语句~一直没搞明白！
select cor_name form [dbo].[A] where [cor_id] = (select top 9 cor_id form [dbo].[B] ORDER BY updatatime DESC)
上面两个条件都满足的话，这一句SQL就可以完成了
kafly at 2006-6-5 16:07:34 >
# 4 Re: 求一SQL语句~一直没搞明白！
肯定不需要重复的啊~
select cor_name form [dbo].[A] where [cor_id] = (select top 9 cor_id form [dbo].[B] ORDER BY updatatime DESC)
是不是有重复的啊？
liujian098 at 2006-6-5 16:14:28 >
# 5 Re: 求一SQL语句~一直没搞明白！
select top 15 cor_name from A where cor_id in (select top 15 cor_id from B order by id desc)
gsyst520 at 2006-6-5 16:15:08 >
# 7 Re: 求一SQL语句~一直没搞明白！
select top 15 a.cor_name from (select 表A.cor_name,表B.id from 表A,表B where 表A.cor_id=表B.cor_id order by 表B.id desc) as a order by a.id desc
可能有点错误,改改!
laifangsong at 2006-6-6 9:03:51 >
# 8 Re: 求一SQL语句~一直没搞明白！
select top 15 A.cor_name from A,B where A.cor_id=B.cor_id group by A.cor_name order by B.uptime(时间) desc
试一下，看对不对，不过第二个表里得有发送信息的时间，
还有，你查一下DISTINCT的语法，看能不能用
zrqgood at 2006-6-6 9:16:52 >
# 9 Re: 求一SQL语句~一直没搞明白！
SELECT DISTINCT TOP 15 A.[cor_name] FROM A INNER JOIN B ON A.[cor_id] = B.[cor_id] ORDER BY B.[updatetime] DESC
xshsoft at 2006-6-6 9:20:57 >
# 10 Re: 求一SQL语句~一直没搞明白！
zrqgood(梦欣) group by A.cor_name order by B.uptime(时间) desc 这样好象不能一起用啊~
这个
select top 15 a.cor_name from (select 表A.cor_name,表B.id from 表A,表B where 表A.cor_id=表B.cor_id order by 表B.id desc) as a order by a.id desc
怎么有问题啊？
liujian098 at 2006-6-8 9:13:33 >
# 11 Re: 求一SQL语句~一直没搞明白！
既然是15个不同企业，那就按企业汇总。最新，那就按汇总后的消息的ID排列。消息的ID是自动增长的。ID大的就是应该是最新的，也不要考虑有没有uptime
select top 15 a.corname from A a,B b where a.cor_id=b.cor_id group by corname order by max(b.id)
daxuejianku at 2006-6-8 10:37:40 >
# 15 Re: 求一SQL语句~一直没搞明白！
select top 15 a.corname from 表A a,表B b where b.cor_id=a.cor_id order by b.id desc
不用group by
要不然就用
select top 15 * from 表A where cor_id in (select DISTINCT(cor_id) from 表B order by cor_id)
INOCracker at 2006-6-9 9:21:09 >
# 17 Re: 求一SQL语句~一直没搞明白！
SELECT cor_name FROM cor WHERE cor_id IN (SELECT TOP 3 cor_id FROM mes GROUP BY cor_id ORDER BY MAX(ID) DESC)
如果除了cor_name外还要其它字段,请将in的子查询改写为连接就可以了.
fcuandy at 2006-6-9 10:49:20 >
# 19 Re: 求一SQL语句~一直没搞明白！
1. B 表中得有时间字段，不然不好定义新15条
Select top 15 b.cor_id,b.message
from tabel1 a join table2 b on a.cor_id=b.cord_id
order by time desc
lovetxp at 2006-6-9 14:33:03 >
# 20 Re: 求一SQL语句~一直没搞明白！
1. B 表中得有时间字段，不然不好定义新15条
Select top 15 b.cor_id,b.message
from tabel1 a join table2 b on a.cor_id=b.cord_id
order by b.time desc
lovetxp at 2006-6-9 14:34:33 >