select *
from tab a
where id in (
select top 3 id
from tab
where anclassid = a.anclassid
order by id
)
order by anclassid desc,id
from tab a
where id in (
select top 3 id
from tab
where anclassid = a.anclassid
order by id
)
order by anclassid desc,id
drop table tb
go
create table tb([id] int,[name] varchar(10),[anclassid] int)
insert tb select 1,'aaaa',9
insert tb select 2,'bbbb',12
insert tb select 3,'cccc',1
insert tb select 4,'dddd',5
insert tb select 5,'eeee',12
insert tb select 6,'ffff',12
insert tb select 7,'gggg',9
insert tb select 8,'hhhh',12
insert tb select 9,'iiii',1
insert tb select 10,'jjjj',9
insert tb select 11,'kkkk',12
insert tb select 12,'llll',5
insert tb select 13,'oooo',5
insert tb select 14,'pppp',1
insert tb select 15,'qqqq',9
goselect *
from tb t
where id in
(select top 3 id from tb where [anclassid]=t.[anclassid] order by [anclassid] desc)
order by [anclassid] desc/*
id name anclassid
----------- ---------- -----------
2 bbbb 12
5 eeee 12
6 ffff 12
7 gggg 9
1 aaaa 9
10 jjjj 9
12 llll 5
13 oooo 5
4 dddd 5
3 cccc 1
9 iiii 1
14 pppp 1(12 行受影响)
*/
drop table tb
go
create table tb([id] int,[name] varchar(10),[anclassid] int)
insert tb select 1,'aaaa',9
insert tb select 2,'bbbb',12
insert tb select 3,'cccc',1
insert tb select 4,'dddd',5
insert tb select 5,'eeee',12
insert tb select 6,'ffff',12
insert tb select 7,'gggg',9
insert tb select 8,'hhhh',12
insert tb select 9,'iiii',1
insert tb select 10,'jjjj',9
insert tb select 11,'kkkk',12
insert tb select 12,'llll',5
insert tb select 13,'oooo',5
insert tb select 14,'pppp',1
insert tb select 15,'qqqq',9
goselect *
from tb t
where id in
(select top 3 id from tb where [anclassid]=t.[anclassid] order by [anclassid] desc)
order by [anclassid] desc/*
id name anclassid
----------- ---------- -----------
2 bbbb 12
5 eeee 12
6 ffff 12
7 gggg 9
1 aaaa 9
10 jjjj 9
12 llll 5
13 oooo 5
4 dddd 5
3 cccc 1
9 iiii 1
14 pppp 1(12 行受影响)
*/
select id,name,anclassid
from tb t
where id in(select top 3 id from tb where t.anclassid = anclassid order by id)
order by anclassid desc