select id from table group by id having count(*)>5
--创建测试环境 create table # ( id int identity, name varchar(20), type int check(type between 1 and 5) ) insert #(name,type) select 'A',1 union select 'B',1 union select 'C',1 union select 'D',1 union select 'E',1 union select 'F',1 union select 'G',1 union select 'B',2 union select 'A',2 union select 'B',2 union select 'A',2 union select 'B',3 union select 'A',2 union select 'B',5--测试 select * from # A where (select count(*) from # where type=A.type and id<A.id)<5 order by type,id --删除测试环境 drop table # --结果 /*id name type ----------- -------------------- ----------- 3 B 1 1 A 1 7 C 1 8 D 1 9 E 1 2 A 2 4 B 2 5 B 3 6 B 5(所影响的行数为 9 行) */
用下面語句,可得到每种类型各5条记录。select top 5 a.* from table a, (select type, count(type) count1 from table group by type)b where a.type=b.type and b.count1>=5
group by id
having count(*)>5
create table #
(
id int identity,
name varchar(20),
type int check(type between 1 and 5)
)
insert #(name,type)
select 'A',1 union
select 'B',1 union
select 'C',1 union
select 'D',1 union
select 'E',1 union
select 'F',1 union
select 'G',1 union
select 'B',2 union
select 'A',2 union
select 'B',2 union
select 'A',2 union
select 'B',3 union
select 'A',2 union
select 'B',5--测试
select * from # A where (select count(*) from # where type=A.type and id<A.id)<5 order by type,id
--删除测试环境
drop table # --结果
/*id name type
----------- -------------------- -----------
3 B 1
1 A 1
7 C 1
8 D 1
9 E 1
2 A 2
4 B 2
5 B 3
6 B 5(所影响的行数为 9 行)
*/
from table a, (select type, count(type) count1 from table group by type)b
where a.type=b.type and b.count1>=5