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 allselect 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
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)))
select row_number()over(partition by [ID],[Name] order by [Date] asc) as num,
* from tbl)a
where num=1--这个取出了你说的那样分组去最小日期,别的字段你处理一下就好了吧
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 allselect '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
*/
这个方法,status字段的内容怎么体现呢
4楼的方法简单可行。
我给出另一种思路:select ID,Name,
'New'as statusNew,'Old' as statusOld ,
(select min(CONVERT(varchar,Date,23)) from #T where ID =t.ID and Name=t.Name and Status='New') Newdate,
(select max(CONVERT(varchar,Date,23)) from #T where ID =t.ID and Name=t.Name and Status='Old') OldDate
from #T t
group by ID,Name
order by Name运行结果
BB89237B-B43C-40D5-8D8E-1140492FB9F4 A New Old 2011-10-01 2011-10-05
BB89237B-B43C-40D5-8D8E-1140492FB9F3 B New Old 2011-01-01 2011-01-02