表中 有类别这个字段,要求每类取最新的几条条,这咋写??谢谢~!
ID TypeID Name Time
1 1 测试1 2008-1-1
2 1 测试2 2008-2-1
3 2 测试3 2008-3-1
4 2 测试4 2008-4-1我想要的结果是
ID TypeID Name
2 1 测试2
4 2 测试4
当然,数据和类别不只是这些,会很多
ID TypeID Name Time
1 1 测试1 2008-1-1
2 1 测试2 2008-2-1
3 2 测试3 2008-3-1
4 2 测试4 2008-4-1我想要的结果是
ID TypeID Name
2 1 测试2
4 2 测试4
当然,数据和类别不只是这些,会很多
from ta a
where not exists(select 1 from ta where typeid = a.typeid and id > a.id)
select ID,TypeID,Name
From
(
select ID,TypeID,Name ,
rn=row_number() over(Partition by TypeID order by Time DESC)
from 表
) t
where t.rn<=1
create table #Y
(
ID int,
TypeID int,
Name nvarchar(20),
Time datetime
)
insert into #Y
select
1 , 1 , '测试1', '2008-1-1' union all
select
2 , 1 , '测试2', '2008-2-1' union all
select
3 , 2 , '测试3', '2008-3-1' union all
select
4 , 2 , '测试4', '2008-4-1'
select ID,TypeID,Name,Time from
(
select ID,TypeID,Name,Time,ROW_NUMBER()over(partition by TypeID order by Time desc) rank1
from #Y
) A
where rank1=1
--借楼上数据一用
create table #Y
(
ID int,
TypeID int,
Name nvarchar(20),
Time datetime
)
insert into #Y
select
1 , 1 , '测试1', '2008-1-1' union all
select
2 , 1 , '测试2', '2008-2-1' union all
select
3 , 2 , '测试3', '2008-3-1' union all
select
4 , 2 , '测试4', '2008-4-1'
--1.
select * from #Y ta
where not exists (select 1 from #Y where typeid=ta.typeid and time>ta.time)
--2.
select ta.* from #Y ta,
(
select TypeID,Time=max(Time)
from #Y
group by TypeID
) tb
where ta.TypeID=tb.TypeID and ta.Time=tb.Time
go
drop table #y
/*
ID TypeID Name Time
----------- ----------- -------------------- -----------------------
2 1 测试2 2008-02-01 00:00:00.000
4 2 测试4 2008-04-01 00:00:00.000(2 行受影响)ID TypeID Name Time
----------- ----------- -------------------- -----------------------
4 2 测试4 2008-04-01 00:00:00.000
2 1 测试2 2008-02-01 00:00:00.000(2 行受影响)
*/
(
ID int,
TypeID int,
Name nvarchar(20),
Time datetime
)
insert into @TB
select 1 , 1 , '测试1', '2008-1-1' union all
select 2 , 1 , '测试2', '2008-2-1' union all
select 3 , 1 , '测试2', '2008-3-1' union all
select 4 , 2 , '测试3', '2008-3-1' union all
select 5 , 2 , '测试4', '2008-4-1' union all
select 6 , 2 , '测试4', '2008-5-1'
SELECT * FROM @TB A
WHERE (SELECT COUNT(1) FROM @TB WHERE TypeID=A.TypeID AND ID<=A.ID)<=2 --这里写上你要取的每类的条数
/*
ID TypeID Name Time
----------- ----------- -------------------- ------------------------------------------------------
1 1 测试1 2008-01-01 00:00:00.000
2 1 测试2 2008-02-01 00:00:00.000
4 2 测试3 2008-03-01 00:00:00.000
5 2 测试4 2008-04-01 00:00:00.000(所影响的行数为 4 行)
*/
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
)