Create table #T([ID] uniqueidentifier,[Name] nvarchar(1),[Status] nvarchar(2),[Date] Datetime)
Insert #T
select BB89237B-B43C-40D5-8D8E-1140492FB9F4,N'A',N'New',N'2011-10-1' union all
select BB89237B-B43C-40D5-8D8E-1140492FB9F4,N'A',N'New',N'2011-10-2' union all
select BB89237B-B43C-40D5-8D8E-1140492FB9F4,N'A',N'Old',N'2011-10-1' union all
select BB89237B-B43C-40D5-8D8E-1140492FB9F4,N'A',N'Old',N'2011-10-5' union all
select BB89237B-B43C-40D5-8D8E-1140492FB9F4,N'A',N'Per',N'2011-10-1' union all

select BB89237B-B43C-40D5-8D8E-1140492FB9F3,N'B',N'New',N'2011-1-1' union all
select BB89237B-B43C-40D5-8D8E-1140492FB9F3,N'B',N'Old',N'2011-1-2' union all
select BB89237B-B43C-40D5-8D8E-1140492FB9F3,N'B',N'Old',N'2011-1-1' union all
select BB89237B-B43C-40D5-8D8E-1140492FB9F3,N'B',N'Per',N'2011-1-1' 

希望得到结果：(按照ID和name分组，取分组中的New最小时间，和Old的最大时间)
di    name      statusNew   statusOld       Newdate   OldDate
BB89237B-B43C-40D5-8D8E-1140492FB9F4     A   New   Old   2011-10-1   2011-10-5 
BB89237B-B43C-40D5-8D8E-1140492FB9F3     B   New   Old   2011-1-1    2011-1-2

USE TEMPDB
GO
IF OBJECT_ID('#T') IS NOT NULL DROP TABLE #T
GO
Create table #T([ID] uniqueidentifier,[Name] nvarchar(100),[Status] nvarchar(200),[Date] Datetime)
Insert #T
select 'BB89237B-B43C-40D5-8D8E-1140492FB9F4',N'A',N'New',N'2011-10-1' union all
select 'BB89237B-B43C-40D5-8D8E-1140492FB9F4',N'A',N'New',N'2011-10-2' union all
select 'BB89237B-B43C-40D5-8D8E-1140492FB9F4',N'A',N'Old',N'2011-10-1' union all
select 'BB89237B-B43C-40D5-8D8E-1140492FB9F4',N'A',N'Old',N'2011-10-5' union all
select 'BB89237B-B43C-40D5-8D8E-1140492FB9F4',N'A',N'Per',N'2011-10-1' union all

select 'BB89237B-B43C-40D5-8D8E-1140492FB9F3',N'B',N'New',N'2011-1-1' union all
select 'BB89237B-B43C-40D5-8D8E-1140492FB9F3',N'B',N'Old',N'2011-1-2' union all
select 'BB89237B-B43C-40D5-8D8E-1140492FB9F3',N'B',N'Old',N'2011-1-1' union all
select 'BB89237B-B43C-40D5-8D8E-1140492FB9F3',N'B',N'Per',N'2011-1-1' 
GO
SELECT ID,NAME,MIN(CASE WHEN STATUS='NEW' THEN DATE ELSE NULL END) AS [NEW_MIN]
,MAX(CASE WHEN STATUS='OLD' THEN DATE ELSE NULL END) AS [OLD_MAX]
FROM #T
GROUP BY ID,NAME
/*
ID    NAME    NEW_MIN    OLD_MAX
BB89237B-B43C-40D5-8D8E-1140492FB9F4    A    2011-10-01 00:00:00.000    2011-10-05 00:00:00.000
BB89237B-B43C-40D5-8D8E-1140492FB9F3    B    2011-01-01 00:00:00.000    2011-01-02 00:00:00.000
*/

------解决方案--------------------
三姐，使用exists选取最大的，效率和rownumber哪个高？
探讨

SQL code


select *
from tb t
where not exists (select 1 from tb where id=t.id and name=t.name
                  and (new<t.new or (new=t.new and old>t.old)))


------解决方案--------------------
探讨


引用:

SQL code
USE TEMPDB
GO
IF OBJECT_ID('#T') IS NOT NULL DROP TABLE #T
GO
Create table #T([ID] uniqueidentifier,[Name] nvarchar(100),[Status] nvarchar(200),[Date] Dateti……


------解决方案--------------------
探讨

SQL code

USE TEMPDB
GO
IF OBJECT_ID('#T') IS NOT NULL DROP TABLE #T
GO
Create table #T([ID] uniqueidentifier,[Name] nvarchar(100),[Status] nvarchar(200),[Date] Datetime)
Insert #T
select 'BB8……