参考:create table A(Score int ,Shift varchar(10)) insert A select 20,'B' union all select 30,'B' union all select 10,'B' union all select 78,'A' union all select 19,'A' union all select 5 ,'A' union all select 40,'C' union all select 25,'C' union all select 6 ,'C' --select * from Acreate table B(Score int,Shift varchar(10)) insert B select 20,'B' --select * from Bselect * from A i where score in ( select top 2 score from A where Shift=i.Shift and score not in(select score from B where Shift=A.Shift) )drop table A,B
select a.* from 表 a where a.ID in(select top 5 ID from 表 where 组名=a.组名 order by ID)
按如下表取某个字段(ID)分类后同时按时间(rdate)升序、降序前三条记录。if object_id('tempdb..#tab') is not null drop table #tab GO create table #tab ( [id] [char](10),[age] [int],[rdate] [datetime])insert into #tab(id,age,rdate) values('a' , 1 , '2006-01-01') insert into #tab(id,age,rdate) values('a' , 2 , '2006-01-02') insert into #tab(id,age,rdate) values('a' , 3 , '2006-01-03') insert into #tab(id,age,rdate) values('a' , 4 , '2006-01-04') insert into #tab(id,age,rdate) values('a' , 5 , '2006-01-05') insert into #tab(id,age,rdate) values('a' , 6 , '2006-01-06') insert into #tab(id,age,rdate) values('b' , 1 , '2006-02-01') insert into #tab(id,age,rdate) values('b' , 2 , '2006-02-02') insert into #tab(id,age,rdate) values('b' , 3 , '2006-02-03') insert into #tab(id,age,rdate) values('b' , 4 , '2006-02-04') insert into #tab(id,age,rdate) values('c' , 1 , '2006-03-01') insert into #tab(id,age,rdate) values('c' , 2 , '2006-03-02') insert into #tab(id,age,rdate) values('c' , 3 , '2006-03-03') insert into #tab(id,age,rdate) values('d' , 1 , '2006-04-01') insert into #tab(id,age,rdate) values('d' , 2 , '2006-04-02') insert into #tab(id,age,rdate) values('e' , 1 , '2006-05-01')--按时间rdate升序取前三条 select * from #tab t where rdate in ( select top 3 rdate from #tab where id=t.id order by rdate )id age rdate ---------- ----------- ------------------------------------------------------ a 1 2006-01-01 00:00:00.000 a 2 2006-01-02 00:00:00.000 a 3 2006-01-03 00:00:00.000 b 1 2006-02-01 00:00:00.000 b 2 2006-02-02 00:00:00.000 b 3 2006-02-03 00:00:00.000 c 1 2006-03-01 00:00:00.000 c 2 2006-03-02 00:00:00.000 c 3 2006-03-03 00:00:00.000 d 1 2006-04-01 00:00:00.000 d 2 2006-04-02 00:00:00.000 e 1 2006-05-01 00:00:00.000(所影响的行数为 12 行)--按时间rdate降序取前三条 select * from #tab t where rdate in ( select top 3 rdate from #tab where id=t.id order by rdate desc ) order by id , rdate descid age rdate ---------- ----------- ------------------------------------------------------ a 6 2006-01-06 00:00:00.000 a 5 2006-01-05 00:00:00.000 a 4 2006-01-04 00:00:00.000 b 4 2006-02-04 00:00:00.000 b 3 2006-02-03 00:00:00.000 b 2 2006-02-02 00:00:00.000 c 3 2006-03-03 00:00:00.000 c 2 2006-03-02 00:00:00.000 c 1 2006-03-01 00:00:00.000 d 2 2006-04-02 00:00:00.000 d 1 2006-04-01 00:00:00.000 e 1 2006-05-01 00:00:00.000 (所影响的行数为 12 行) --上面包含了总数不到3个的记录(即id为d,e的数据),如果要取消它们,以升序为例(降序同理) select * from #tab m where rdate in ( select top 3 rdate from ( select * from #tab t where id in ( select id from #tab group by id having(count(*)) >= 3 ) ) n where m.id = n.id order by rdate )id age rdate ---------- ----------- ------------------------------------------------------ a 1 2006-01-01 00:00:00.000 a 2 2006-01-02 00:00:00.000 a 3 2006-01-03 00:00:00.000 b 1 2006-02-01 00:00:00.000 b 2 2006-02-02 00:00:00.000 b 3 2006-02-03 00:00:00.000 c 1 2006-03-01 00:00:00.000 c 2 2006-03-02 00:00:00.000 c 3 2006-03-03 00:00:00.000 (所影响的行数为 9 行)--在上面的例中我们发现rdate都是不相同的,如果相同怎么办? --例如id=a,第三条,第四条rdate相同,都为2006-01-03。 id age rdate ---------- ----------- ------------------------------------------------------ a 1 2006-01-01 00:00:00.000 a 2 2006-01-02 00:00:00.000 a 3 2006-01-03 00:00:00.000 a 4 2006-01-03 00:00:00.000 a 5 2006-01-05 00:00:00.000 a 6 2006-01-06 00:00:00.000 b 1 2006-02-01 00:00:00.000 b 2 2006-02-02 00:00:00.000 b 3 2006-02-03 00:00:00.000 b 4 2006-02-04 00:00:00.000 c 1 2006-03-01 00:00:00.000 c 2 2006-03-02 00:00:00.000 c 3 2006-03-03 00:00:00.000 d 1 2006-04-01 00:00:00.000 d 2 2006-04-02 00:00:00.000 e 1 2006-05-01 00:00:00.000--如果想把第三、四都取出来,使用上面的语句即可。如果只取一条(只取第三不取第四)则要使用临时表了。 if object_id('tempdb..#tab') is not null drop table #tab GO if object_id('tempdb..#temp') is not null drop table #temp GO create table #tab ( [id] [char](10),[age] [int],[rdate] [datetime])insert into #tab(id,age,rdate) values('a' , 1 , '2006-01-01') insert into #tab(id,age,rdate) values('a' , 2 , '2006-01-02') insert into #tab(id,age,rdate) values('a' , 3 , '2006-01-03') insert into #tab(id,age,rdate) values('a' , 4 , '2006-01-03') insert into #tab(id,age,rdate) values('a' , 5 , '2006-01-05') insert into #tab(id,age,rdate) values('a' , 6 , '2006-01-06') insert into #tab(id,age,rdate) values('b' , 1 , '2006-02-01') insert into #tab(id,age,rdate) values('b' , 2 , '2006-02-02') insert into #tab(id,age,rdate) values('b' , 3 , '2006-02-03') insert into #tab(id,age,rdate) values('b' , 4 , '2006-02-04') insert into #tab(id,age,rdate) values('c' , 1 , '2006-03-01') insert into #tab(id,age,rdate) values('c' , 2 , '2006-03-02') insert into #tab(id,age,rdate) values('c' , 3 , '2006-03-03') insert into #tab(id,age,rdate) values('d' , 1 , '2006-04-01') insert into #tab(id,age,rdate) values('d' , 2 , '2006-04-02') insert into #tab(id,age,rdate) values('e' , 1 , '2006-05-01')--按时间rdate升序取前三条(其他方法同上) select id1=identity(int,1,1),* into #temp from #tab order by id , rdate /*(降序用rdate desc)*/ select * from #temp t where id1 in ( select top 3 id1 from #temp where id=t.id order by id1 )
declare @sql varchar(8000) set @sql='' select @sql=@sql='select top 5 * from tb where 组名 ='+rtrim(a.组名)+' order by ID union all ' from (select distinct 组名 from tb) a select @sql=left(@sql,len(@sql)-10) exec(@sql)
insert A
select 20,'B' union all
select 30,'B' union all
select 10,'B' union all
select 78,'A' union all
select 19,'A' union all
select 5 ,'A' union all
select 40,'C' union all
select 25,'C' union all
select 6 ,'C'
--select * from Acreate table B(Score int,Shift varchar(10))
insert B select 20,'B'
--select * from Bselect * from A i
where score in
(
select top 2 score from A where Shift=i.Shift and score not in(select score from B where Shift=A.Shift)
)drop table A,B
a.*
from
表 a
where
a.ID in(select top 5 ID from 表 where 组名=a.组名 order by ID)
drop table #tab
GO
create table #tab (
[id] [char](10),[age] [int],[rdate] [datetime])insert into #tab(id,age,rdate) values('a' , 1 , '2006-01-01')
insert into #tab(id,age,rdate) values('a' , 2 , '2006-01-02')
insert into #tab(id,age,rdate) values('a' , 3 , '2006-01-03')
insert into #tab(id,age,rdate) values('a' , 4 , '2006-01-04')
insert into #tab(id,age,rdate) values('a' , 5 , '2006-01-05')
insert into #tab(id,age,rdate) values('a' , 6 , '2006-01-06')
insert into #tab(id,age,rdate) values('b' , 1 , '2006-02-01')
insert into #tab(id,age,rdate) values('b' , 2 , '2006-02-02')
insert into #tab(id,age,rdate) values('b' , 3 , '2006-02-03')
insert into #tab(id,age,rdate) values('b' , 4 , '2006-02-04')
insert into #tab(id,age,rdate) values('c' , 1 , '2006-03-01')
insert into #tab(id,age,rdate) values('c' , 2 , '2006-03-02')
insert into #tab(id,age,rdate) values('c' , 3 , '2006-03-03')
insert into #tab(id,age,rdate) values('d' , 1 , '2006-04-01')
insert into #tab(id,age,rdate) values('d' , 2 , '2006-04-02')
insert into #tab(id,age,rdate) values('e' , 1 , '2006-05-01')--按时间rdate升序取前三条
select * from #tab t
where rdate in
(
select top 3 rdate from #tab where id=t.id order by rdate
)id age rdate
---------- ----------- ------------------------------------------------------
a 1 2006-01-01 00:00:00.000
a 2 2006-01-02 00:00:00.000
a 3 2006-01-03 00:00:00.000
b 1 2006-02-01 00:00:00.000
b 2 2006-02-02 00:00:00.000
b 3 2006-02-03 00:00:00.000
c 1 2006-03-01 00:00:00.000
c 2 2006-03-02 00:00:00.000
c 3 2006-03-03 00:00:00.000
d 1 2006-04-01 00:00:00.000
d 2 2006-04-02 00:00:00.000
e 1 2006-05-01 00:00:00.000(所影响的行数为 12 行)--按时间rdate降序取前三条
select * from #tab t
where rdate in
(
select top 3 rdate from #tab where id=t.id order by rdate desc
)
order by id , rdate descid age rdate
---------- ----------- ------------------------------------------------------
a 6 2006-01-06 00:00:00.000
a 5 2006-01-05 00:00:00.000
a 4 2006-01-04 00:00:00.000
b 4 2006-02-04 00:00:00.000
b 3 2006-02-03 00:00:00.000
b 2 2006-02-02 00:00:00.000
c 3 2006-03-03 00:00:00.000
c 2 2006-03-02 00:00:00.000
c 1 2006-03-01 00:00:00.000
d 2 2006-04-02 00:00:00.000
d 1 2006-04-01 00:00:00.000
e 1 2006-05-01 00:00:00.000
(所影响的行数为 12 行)
--上面包含了总数不到3个的记录(即id为d,e的数据),如果要取消它们,以升序为例(降序同理)
select * from #tab m
where rdate in
(
select top 3 rdate from
(
select * from #tab t
where id in
(
select id from #tab group by id having(count(*)) >= 3
)
) n
where m.id = n.id order by rdate
)id age rdate
---------- ----------- ------------------------------------------------------
a 1 2006-01-01 00:00:00.000
a 2 2006-01-02 00:00:00.000
a 3 2006-01-03 00:00:00.000
b 1 2006-02-01 00:00:00.000
b 2 2006-02-02 00:00:00.000
b 3 2006-02-03 00:00:00.000
c 1 2006-03-01 00:00:00.000
c 2 2006-03-02 00:00:00.000
c 3 2006-03-03 00:00:00.000
(所影响的行数为 9 行)--在上面的例中我们发现rdate都是不相同的,如果相同怎么办?
--例如id=a,第三条,第四条rdate相同,都为2006-01-03。
id age rdate
---------- ----------- ------------------------------------------------------
a 1 2006-01-01 00:00:00.000
a 2 2006-01-02 00:00:00.000
a 3 2006-01-03 00:00:00.000
a 4 2006-01-03 00:00:00.000
a 5 2006-01-05 00:00:00.000
a 6 2006-01-06 00:00:00.000
b 1 2006-02-01 00:00:00.000
b 2 2006-02-02 00:00:00.000
b 3 2006-02-03 00:00:00.000
b 4 2006-02-04 00:00:00.000
c 1 2006-03-01 00:00:00.000
c 2 2006-03-02 00:00:00.000
c 3 2006-03-03 00:00:00.000
d 1 2006-04-01 00:00:00.000
d 2 2006-04-02 00:00:00.000
e 1 2006-05-01 00:00:00.000--如果想把第三、四都取出来,使用上面的语句即可。如果只取一条(只取第三不取第四)则要使用临时表了。
if object_id('tempdb..#tab') is not null
drop table #tab
GO
if object_id('tempdb..#temp') is not null
drop table #temp
GO
create table #tab (
[id] [char](10),[age] [int],[rdate] [datetime])insert into #tab(id,age,rdate) values('a' , 1 , '2006-01-01')
insert into #tab(id,age,rdate) values('a' , 2 , '2006-01-02')
insert into #tab(id,age,rdate) values('a' , 3 , '2006-01-03')
insert into #tab(id,age,rdate) values('a' , 4 , '2006-01-03')
insert into #tab(id,age,rdate) values('a' , 5 , '2006-01-05')
insert into #tab(id,age,rdate) values('a' , 6 , '2006-01-06')
insert into #tab(id,age,rdate) values('b' , 1 , '2006-02-01')
insert into #tab(id,age,rdate) values('b' , 2 , '2006-02-02')
insert into #tab(id,age,rdate) values('b' , 3 , '2006-02-03')
insert into #tab(id,age,rdate) values('b' , 4 , '2006-02-04')
insert into #tab(id,age,rdate) values('c' , 1 , '2006-03-01')
insert into #tab(id,age,rdate) values('c' , 2 , '2006-03-02')
insert into #tab(id,age,rdate) values('c' , 3 , '2006-03-03')
insert into #tab(id,age,rdate) values('d' , 1 , '2006-04-01')
insert into #tab(id,age,rdate) values('d' , 2 , '2006-04-02')
insert into #tab(id,age,rdate) values('e' , 1 , '2006-05-01')--按时间rdate升序取前三条(其他方法同上)
select id1=identity(int,1,1),* into #temp from #tab order by id , rdate /*(降序用rdate desc)*/
select * from #temp t
where id1 in
(
select top 3 id1 from #temp where id=t.id order by id1
)
set @sql=''
select @sql=@sql='select top 5 * from tb where 组名 ='+rtrim(a.组名)+' order by ID union all
' from (select distinct 组名 from tb) a
select @sql=left(@sql,len(@sql)-10)
exec(@sql)