--建立测试环境 Create Table TEST (ID Int Identity(1,1), CID Int, Title Varchar(10)) --插入数据 Insert TEST Values(1, 'aa') Insert TEST Values(1, 'bb') Insert TEST Values(1, 'cc') Insert TEST Values(1, 'dd') Insert TEST Values(1, 'ee') Insert TEST Values(1, 'ff') Insert TEST Values(2, 'dd') Insert TEST Values(2, 'dd') Insert TEST Values(2, 'mm') Insert TEST Values(2, 'kk') Insert TEST Values(2, 'zz') Insert TEST Values(2, 'tt') Insert TEST Values(2, 'ee') Insert TEST Values(3, 'dd') --测试 Select * from TEST A Where Not Exists(Select 1 from TEST Where ID<=A.ID And CID=A.CID Having Count(1)>5) --删除测试环境 Drop Table TEST --结果 /* ID CID Title 1 1 aa 2 1 bb 3 1 cc 4 1 dd 5 1 ee 7 2 dd 8 2 dd 9 2 mm 10 2 kk 11 2 zz 14 3 dd */
select top 5 * from table1 where cid=1 union all select top 5 * from table1 where cid=2
--建立测试环境
Create Table TEST
(ID Int Identity(1,1),
CID Int,
Title Varchar(10))
--插入数据
Insert TEST Values(1, 'aa')
Insert TEST Values(1, 'bb')
Insert TEST Values(1, 'cc')
Insert TEST Values(1, 'dd')
Insert TEST Values(1, 'ee')
Insert TEST Values(1, 'ff')
Insert TEST Values(2, 'dd')
Insert TEST Values(2, 'dd')
Insert TEST Values(2, 'mm')
Insert TEST Values(2, 'kk')
Insert TEST Values(2, 'zz')
Insert TEST Values(2, 'tt')
Insert TEST Values(2, 'ee')
Insert TEST Values(3, 'dd')
--测试
Select * from TEST A Where Not Exists(Select 1 from TEST Where ID<=A.ID And CID=A.CID Having Count(1)>5)
--删除测试环境
Drop Table TEST
--结果
/*
ID CID Title
1 1 aa
2 1 bb
3 1 cc
4 1 dd
5 1 ee
7 2 dd
8 2 dd
9 2 mm
10 2 kk
11 2 zz
14 3 dd
*/
union all
select top 5 * from table1 where cid=2