数据表 keyId typeId userName addYear addMonth
1 0001 小张 2010 9
2 0001 小李 2011 8
3 0002 小王 2008 7
4 0002 小赵 2006 6
5 0003 小刘 2005 4要求取得每个分类里面注册时间最晚的记录,也就是说取得每个分类里面,年份最近,月份最近的记录集,希望的查询结果是
keyId typeId userName addYear addMonth2 0001 小李 2011 8
3 0002 小王 2008 7
5 0003 小刘 2005 4
1 0001 小张 2010 9
2 0001 小李 2011 8
3 0002 小王 2008 7
4 0002 小赵 2006 6
5 0003 小刘 2005 4要求取得每个分类里面注册时间最晚的记录,也就是说取得每个分类里面,年份最近,月份最近的记录集,希望的查询结果是
keyId typeId userName addYear addMonth2 0001 小李 2011 8
3 0002 小王 2008 7
5 0003 小刘 2005 4
from tb
where addYear in(select max(addYear) from tb);
from tb
where addYear in(select typeId,max(addYear) from tb group by typeId);
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
keyId int identity(1,1),
typeId varchar(10),
userName varchar(10),
addYear int,
addMonth int
)
go
insert into tb (typeId,userName,addYear,addMonth)
select '0001','小张',2010,9 union all
select '0001','小李',2011,8 union all
select '0002','小王',2008,7 union all
select '0002','小赵',2006,6 union all
select '0003','小刘',2005,4
go
select * from tb a where not exists(select 1 from tb where typeId=a.typeId and cast(cast(addYear as varchar)+'-'+cast(addMonth as varchar)+'-01' as datetime)>cast(cast(a.addYear as varchar)+'-'+cast(a.addMonth as varchar)+'-01' as datetime))
go
/*
keyId typeId userName addYear addMonth
----------- ---------- ---------- ----------- -----------
2 0001 小李 2011 8
3 0002 小王 2008 7
5 0003 小刘 2005 4(3 行受影响)
*/
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([keyId] int,[typeId] nvarchar(4),[userName] nvarchar(2),[addYear] int,[addMonth] int)
Insert #T
select 1,N'0001',N'小张','2010',9 union all
select 2,N'0001',N'小李','2011',8 union all
select 3,N'0002',N'小王','2008',7 union all
select 4,N'0002',N'小赵','2006',6 union all
select 5,N'0003',N'小刘','2005',4
Go
Select * from #T as a
where not exists(select 1 from #T where [typeId]=a.[typeId] and [addYear]>a.[addYear] or([addYear]=a.[addYear] and [addMonth]>a.[addMonth]))/*
keyId typeId userName addYear addMonth
2 0001 小李 2011 8
3 0002 小王 2008 7
5 0003 小刘 2005 4
*/
select *
from tb t
where not exists (select 1 from tb where typeid = t.typeid and (addyear>t.addyear or (addyear = t.addyear and addmonth > t.addmonth))
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([keyId] int,[typeId] nvarchar(4),[userName] nvarchar(2),[addYear] int,[addMonth] int)
Insert #T
select 1,N'0001',N'小张','2010',9 union all
select 2,N'0001',N'小李','2011',8 union all
select 3,N'0002',N'小王','2008',7 union all
select 4,N'0002',N'小赵','2006',6 union all
select 5,N'0003',N'小刘','2005',4
Go
Select * from #T as a
where not exists(select 1 from #T where [typeId]=a.[typeId] and [keyId]>a.[keyId])/*
keyId typeId userName addYear addMonth
2 0001 小李 2011 8
4 0002 小赵 2006 6
5 0003 小刘 2005 4
*/
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html
from tb
where addYear in(select typeId,max(addYear) from tb group by typeId);
from tb
where addYear in(select typeId,max(addYear) from tb group by typeId);
SQL05以上版本可这样用select *
from (select *,row_number()over(partition by [typeId] order by [addYear] desc,[addMonth] desc) as row from #T a)T
where row=1
(苦苦的潜行者)
*/
if object_id('t1') is not null
drop table t1
go
create table t1(keyid int,typeid varchar(10),username varchar(10),addyear varchar(10),addmonth varchar(10))
go
insert t1
select 1,'0001','小张','2010','9' union all
select 2,'0001','小李','2011','8' union all
select 3,'0002','小王','2008','7' union all
select 4,'0002','小赵','2006','6' union all
select 5,'0005','小刘','2005','4'
go
select * from t1 a where
not exists (select 1 from t1
where a.typeid=typeid
and convert(varchar(5),a.addyear+'-'+a.addmonth,120)<convert(varchar(5),addyear+'-'+addmonth,120)
)
/*
(所影响的行数为 3 行)
keyid typeid username addyear addmonth
---- ---- ------ ----- ----
2 0001 小李 2011 8
3 0002 小王 2008 7
5 0005 小刘 2005 4
*/
go
drop table t1