前4条?按照ID排序? select top 4 * from ( select distinct * from tb ) t order by id select top 4 * from ( select distinct * from tb ) t order by id desc
--> 测试数据: #ta if object_id('tempdb.dbo.#ta') is not null drop table #ta go create table #ta (ID int,P varchar(2),K int,ICount int) insert into #ta select 1,'A1',1,10 union all select 9,'A2',2,10 union all select 13,'A5',5,10 union all select 14,'A5',6,9 union all select 10,'A2',3,9 union all select 2,'A1',2,9 union all select 5,'A4',4,9 union all select 6,'A4',5,9 union all select 17,'A3',3,9 union all select 18,'A3',4,9 union all select 19,'A3',5,8 union all select 7,'A4',6,8 union all select 3,'A1',3,8 union all select 11,'A2',4,8 union all select 15,'A5',7,8 union all select 16,'A5',8,7 union all select 12,'A2',5,7 union all select 4,'A1',4,7 union all select 8,'A4',7,7 union all select 20,'A3',6,7select top 4 * from #ta t where not exists(select * from #ta where p=t.p and ICount> t.ICount)ID P K ICount ----------- ---- ----------- ----------- 1 A1 1 10 9 A2 2 10 13 A5 5 10 5 A4 4 9(4 行受影响)
select top 4 * from #ta t where not exists(select * from #ta where p=t.p and ICount> t.ICount) order by iCount desc
select p,sum(icount) as co from dbo.tmp group by p order by co desc SQL 2005的测试环境
select top 4 * from
(
select distinct * from tb
) t
order by id select top 4 * from
(
select distinct * from tb
) t
order by id desc
if object_id('tempdb.dbo.#ta') is not null drop table #ta
go
create table #ta (ID int,P varchar(2),K int,ICount int)
insert into #ta
select 1,'A1',1,10 union all
select 9,'A2',2,10 union all
select 13,'A5',5,10 union all
select 14,'A5',6,9 union all
select 10,'A2',3,9 union all
select 2,'A1',2,9 union all
select 5,'A4',4,9 union all
select 6,'A4',5,9 union all
select 17,'A3',3,9 union all
select 18,'A3',4,9 union all
select 19,'A3',5,8 union all
select 7,'A4',6,8 union all
select 3,'A1',3,8 union all
select 11,'A2',4,8 union all
select 15,'A5',7,8 union all
select 16,'A5',8,7 union all
select 12,'A2',5,7 union all
select 4,'A1',4,7 union all
select 8,'A4',7,7 union all
select 20,'A3',6,7select top 4 * from #ta t
where not exists(select * from #ta where p=t.p and ICount> t.ICount)ID P K ICount
----------- ---- ----------- -----------
1 A1 1 10
9 A2 2 10
13 A5 5 10
5 A4 4 9(4 行受影响)
select top 4 * from #ta t
where not exists(select * from #ta where p=t.p and ICount> t.ICount)
order by iCount desc
SQL 2005的测试环境