id userid title publishdate
0 1000 123 2007-4-16 9:27
1 1001 456 2007-4-17 9:27
2 1002 789 2007-4-18 9:27
3 1000 789 2007-4-19 9:27
4 1000 312 2007-4-19 10:27想查询到最近发表过的前三个人,按publishdate降序排序id userid title publishdate
4 1000 312 2007-4-19 10:27
2 1002 789 2007-4-18 9:27
1 1001 456 2007-4-17 9:27
0 1000 123 2007-4-16 9:27
1 1001 456 2007-4-17 9:27
2 1002 789 2007-4-18 9:27
3 1000 789 2007-4-19 9:27
4 1000 312 2007-4-19 10:27想查询到最近发表过的前三个人,按publishdate降序排序id userid title publishdate
4 1000 312 2007-4-19 10:27
2 1002 789 2007-4-18 9:27
1 1001 456 2007-4-17 9:27
from 表名
order by publishdate
A.*
From
TableName A
Where Not Exists(Select userid From TableName Where userid = A.userid And publishdate > A.publishdate)
Order By publishdate Desc
TOP 3
A.*
From
TableName A
Where Not Exists(Select userid From TableName Where userid = A.userid And publishdate > A.publishdate)
Order By publishdate Desc
TOP 3
A.*
From
TableName A
Where publishdate In (Select Max(publishdate) From TableName Where userid = A.userid)
Order By publishdate Desc
TOP 3
A.*
From
TableName A
Inner Join
(Select userid, Max(publishdate) As publishdate From TableName Group By userid)
B
On A.userid = B.userid And A.publishdate = B.publishdate
Order By A.publishdate Desc
Select
TOP 3
A.*
From
TableName A
Where Not Exists(Select userid From TableName Where userid = A.userid And publishdate > A.publishdate)
Order By publishdate Desc--方法二:
Select
TOP 3
A.*
From
TableName A
Where publishdate In (Select Max(publishdate) From TableName Where userid = A.userid)
Order By publishdate Desc--方法三:
Select
TOP 3
A.*
From
TableName A
Inner Join
(Select userid, Max(publishdate) As publishdate From TableName Group By userid)
B
On A.userid = B.userid And A.publishdate = B.publishdate
Order By A.publishdate Desc
insert @ta select 0, 1000, 123, '2007-4-16 9:27'
insert @ta select 1, 1001, 456, '2007-4-17 9:27'
insert @ta select 2, 1002, 789, '2007-4-18 9:27'
insert @ta select 3, 1000, 789, '2007-4-19 9:27'
insert @ta select 4, 1000, 312, '2007-4-19 10:27'
select *
from @ta a
where
(select count(distinct title) from @ta where id>a.id)<3
and not exists
(select 1 from @ta where title=a.title and id<a.id)
order by id desc
(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
id userid title publishdate
----------- ----------- ----------- -----------------------
4 1000 312 2007-04-19 10:27:00
2 1002 789 2007-04-18 09:27:00
1 1001 456 2007-04-17 09:27:00(3 行受影响)
(select top 3 userid,max(publishdate) publishdate from tb5 group by userid) a
left join tb5 b on a.userid=b.userid and a.publishdate=b.publishdate order by a.publishdate desc
insert @ta select 0, 1000, 123, '2007-4-16 9:27'
insert @ta select 1, 1001, 456, '2007-4-17 9:27'
insert @ta select 2, 1002, 789, '2007-4-18 9:27'
insert @ta select 3, 1000, 789, '2007-4-19 9:27'
insert @ta select 4, 1000, 312, '2007-4-19 10:27'
select a.*from @ta a join (select top 3 userid, max(publishdate)publishdate from @ta group by userid) b
on a.userid=b.userid and a.publishdate=b.publishdate order by a.publishdate
insert into # values(0,1000,123,'2007-4-16 9:27')
insert into # values(1,1001,456,'2007-4-17 9:27')
insert into # values(2,1002,789,'2007-4-18 9:27')
insert into # values(3,1000,789,'2007-4-19 9:27')
insert into # values(4,1000,312,'2007-4-19 10:27')select top 3 * from # a
where not exists(select 1 from # where a.userid=userid and a.publishdate<publishdate )
order by publishdate desc
id userid title publishdate
----------- ----------- ----------- ------------------------------------------------------
4 1000 312 2007-04-19 10:27:00.000
2 1002 789 2007-04-18 09:27:00.000
1 1001 456 2007-04-17 09:27:00.000(所影响的行数为 3 行)
Select
TOP 3
A.*
From
TableName A
Inner Join
(Select userid, Max(publishdate) As publishdate From TableName Group By userid)
B
On A.userid = B.userid And A.publishdate = B.publishdate
Order By A.publishdate Desc
insert into userCount values(0,1000,123,'2007-4-16 9:27')
insert into userCount values(1,1001,456,'2007-4-17 9:27')
insert into userCount values(2,1002,789,'2007-4-18 9:27')
insert into userCount values(3,1000,789,'2007-4-19 9:27')
insert into userCount values(4,1000,312,'2007-4-19 10:27')select top 3 * from userCount as userCount1
join (select userid,max(publishdate) as publishdate from userCount group by userid ) userCount3
on userCount1.userid = userCount3.userid
and userCount1.publishdate = userCount3.publishdate
order by userCount1.publishdate desc