有个表，字段是a int,b int,c datetime
假设数据有：
a b c
1 1
2 1
3 1
4 1
1 2
2 2
3 2
4 2
（C时间字段数据未列出）
现在想从表中读取a b c，并且b为1和2的各两条按C排序的最新的纪录
分别查询的话：
select top 2 a,b,c from table where b=1 order by c
select top 2 a,b,c from table where b=2 order by c
即可，但能否用一行来实现？
假设数据有：
a b c
1 1
2 1
3 1
4 1
1 2
2 2
3 2
4 2
（C时间字段数据未列出）
现在想从表中读取a b c，并且b为1和2的各两条按C排序的最新的纪录
分别查询的话：
select top 2 a,b,c from table where b=1 order by c
select top 2 a,b,c from table where b=2 order by c
即可，但能否用一行来实现？
select top 2 a,b,c from table where b=1 order by c
select top 2 a,b,c from table where b=2 order by c
两条语句中间加个union all~
--方法一
Select * From 表 A Where Not Exists(Select c From 表 Where b = A.b And c > A.c)--方法二
Select * From 表 A Where c = (Select Max(c) From 表 Where b = A.b)--方法三
Select A.* From 表 A
Inner Join
(Select b, Max(c) As c From 表 Group By b) B
On A.b = B.b And A.c = B.c
where id in (select id from #temp where b=1 order by c) or
id in (select id from #temp where b=1 order by c)
SELECT identity(int,1,1) id ,* into #temp from tableselect * from #temp
where id in (select top 2 id from #temp where b=1 order by c) or
id in (select top 2 id from #temp where b=2 order by c)