数据库表中有三个字段,ID、A、B,ID是唯一的,不重复,要实现按B分类,取没类中按A排序的前20条记录,该怎么实现?比如:
ID A B
--------------------
1 10 Class1
2 9.5 Class1
... ... ...
20 0.1 Class1
21 0.09 Class1
22 20 Class2
23 19 Class2
... ... ...要实现:
ID A B
--------------------
1 10 Class1
2 9.5 Class1
... ... ...
20 0.1 Class1
22 20 Class2
23 19 Class2
... ... ...(Class1按A排序的前20,Class2按A排序的前20,等等)
ID A B
--------------------
1 10 Class1
2 9.5 Class1
... ... ...
20 0.1 Class1
21 0.09 Class1
22 20 Class2
23 19 Class2
... ... ...要实现:
ID A B
--------------------
1 10 Class1
2 9.5 Class1
... ... ...
20 0.1 Class1
22 20 Class2
23 19 Class2
... ... ...(Class1按A排序的前20,Class2按A排序的前20,等等)
Where ID In(Select TOP 20 ID From TableName Where B=A.B)
Where ID In(Select TOP 20 ID From TableName Where B=A.B order by a desc)
select * from tab a
where id in
(
select top 20 id from tab
where a.b = b
order by a asc
)
group by a.id , a.a , a.b