declare @TestTb table (id int,name varchar(4),age int,createdate varchar(10)) insert into @TestTb select 1,'张三',15,'2011-5-20' union all select 2,'张三',15,'2011-6-20' union all select 3,'张三',15,null union all select 4,'李四',16,'2011-12-31' union all select 5,'李四',17,'2011-12-31' union all select 6,'李四',16,'2012-1-29' union all select 7,'王五',17,'2011-1-1' union all select 8,'赵六',18,nullselect * from @TestTb a where createdate= (select max(createdate) from @TestTb where name=a.name) union select * from @TestTb where createdate is null/* id name age createdate ----------- ---- ----------- ---------- 2 张三 15 2011-6-20 3 张三 15 NULL 6 李四 16 2012-1-29 7 王五 17 2011-1-1 8 赵六 18 NULL */
select * from testtb t where not exists(select 1 from testtb where name=t.name and createdate>t.createdate or (createdate=t.createdate and id<t.id))
Select * from TestTb as a where not exists(select 1 from TestTb where [name]=a.[name] and ([createdate]>a.[createdate] or ([createdate]=a.[createdate] and ID>a.ID) ))这样用
同小F答案贴重了可用row_number --> --> (Roy)生成測試數據
if not object_id('TestTb') is null drop table TestTb Go Create table TestTb([id] int,[name] nvarchar(2),[age] int,[createdate] Datetime) Insert TestTb select 1,N'张三',15,'2011-5-20' union all select 2,N'张三',15,'2011-6-20' union all select 3,N'张三',15,null union all select 4,N'李四',16,'2011-12-31' union all select 5,N'李四',17,'2011-12-31' union all select 6,N'李四',16,'2012-1-29' union all select 7,N'王五',17,'2011-1-1' union all select 8,N'赵六',18,null Go select * from (Select *,row=ROW_NUMBER()over(partition by [name],case when[createdate] is null then 1 else 0 end order by [createdate] desc,ID desc ) from TestTb) as a where row=1 order by ID
declare @TestTb table
(id int,name varchar(4),age int,createdate varchar(10))
insert into @TestTb
select 1,'张三',15,'2011-5-20' union all
select 2,'张三',15,'2011-6-20' union all
select 3,'张三',15,null union all
select 4,'李四',16,'2011-12-31' union all
select 5,'李四',17,'2011-12-31' union all
select 6,'李四',16,'2012-1-29' union all
select 7,'王五',17,'2011-1-1' union all
select 8,'赵六',18,nullselect * from @TestTb a where createdate=
(select max(createdate) from @TestTb where name=a.name)
union
select * from @TestTb where createdate is null/*
id name age createdate
----------- ---- ----------- ----------
2 张三 15 2011-6-20
3 张三 15 NULL
6 李四 16 2012-1-29
7 王五 17 2011-1-1
8 赵六 18 NULL
*/
*
from
testtb t
where
not exists(select 1 from testtb where name=t.name and createdate>t.createdate or (createdate=t.createdate and id<t.id))
where not exists(select 1 from TestTb where [name]=a.[name] and
([createdate]>a.[createdate] or ([createdate]=a.[createdate] and ID>a.ID) ))这样用
--> --> (Roy)生成測試數據
if not object_id('TestTb') is null
drop table TestTb
Go
Create table TestTb([id] int,[name] nvarchar(2),[age] int,[createdate] Datetime)
Insert TestTb
select 1,N'张三',15,'2011-5-20' union all
select 2,N'张三',15,'2011-6-20' union all
select 3,N'张三',15,null union all
select 4,N'李四',16,'2011-12-31' union all
select 5,N'李四',17,'2011-12-31' union all
select 6,N'李四',16,'2012-1-29' union all
select 7,N'王五',17,'2011-1-1' union all
select 8,N'赵六',18,null
Go
select *
from
(Select *,row=ROW_NUMBER()over(partition by [name],case when[createdate] is null then 1 else 0 end order by [createdate] desc,ID desc ) from TestTb) as a
where row=1
order by ID