问一个简单的SQL语句，请教各位大神。
RowId Id Name EditDate
1 11 Wang 2013/4/5
2 11 Wang 2013/5/31
3 11 Wang 2013/6/1
4 12 Zhang 2013/4/5
5 12 Zhang 2013/6/1
结果要求输出相同Id和Name最新一条。
------解决方案--------------------
select *
from
(select *,row_number() over (partition by Id order by EditDate desc) re) a
where re=1
------解决方案--------------------
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-09-23 12:48:25
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([RowId] int,[Id] int,[Name] varchar(5),[EditDate] datetime)
insert [huang]
select 1,11,'Wang','2013/4/5' union all
select 2,11,'Wang','2013/5/31' union all
select 3,11,'Wang','2013/6/1' union all
select 4,12,'Zhang','2013/4/5' union all
select 5,12,'Zhang','2013/6/1'
--------------开始查询--------------------------
select *
from [huang] a
WHERE EXISTS (SELECT 1 FROM (SELECT id,name,MAX(editdate)editdate FROM huang GROUP BY id,name)b WHERE a.editdate=b.editdate AND a.id=b.id AND a.NAME=b.name)
----------------结果----------------------------
/*
RowId Id Name EditDate
----------- ----------- ----- -----------------------
3 11 Wang 2013-06-01 00:00:00.000
5 12 Zhang 2013-06-01 00:00:00.000
*/
------解决方案--------------------
;with cte(RowId,Id,Name,EditDate) as
(
select 1,11,'Wang','2013/4/5'
union all select 2,11,'Wang','2013/5/31'
union all select 3,11,'Wang','2013/6/1'
union all select 4,12,'Zhang','2013/4/5'
union all select 5,12,'Zhang','2013/6/1'
)
select RowId,Id,Name,EditDate
from
(
select *,rn=ROW_NUMBER() over(partition by Id,Name order by EditDate desc) from cte
)t
where rn=1
/*
RowId Id Name EditDate
3 11 Wang 2013/6/1
5 12 Zhang 2013/6/1
*/
------解决方案--------------------
--还有一种方法:
;with cte(RowId,Id,Name,EditDate) as
(
select 1,11,'Wang','2013/4/5'
union all select 2,11,'Wang','2013/5/31'
union all select 3,11,'Wang','2013/6/1'
union all select 4,12,'Zhang','2013/4/5'
union all select 5,12,'Zhang','2013/6/1'
)
select *
from cte a
where not exists (select 1 from cte b where a.Id=b.Id and a.Name=b.Name and b.EditDate>a.EditDate)
/*
RowId Id Name EditDate
3 11 Wang 2013/6/1
5 12 Zhang 2013/6/1
*/