sql分组且分组按条件取大小,该如何解决

sql分组且分组按条件取大小

SQL code

Create table #T([ID] uniqueidentifier,[Name] nvarchar(1),[Status] nvarchar(2),[Date] Datetime)Insert #Tselect BB89237B-B43C-40D5-8D8E-1140492FB9F4,N'A',N'New',N'2011-10-1' union allselect BB89237B-B43C-40D5-8D8E-1140492FB9F4,N'A',N'New',N'2011-10-2' union allselect BB89237B-B43C-40D5-8D8E-1140492FB9F4,N'A',N'Old',N'2011-10-1' union allselect BB89237B-B43C-40D5-8D8E-1140492FB9F4,N'A',N'Old',N'2011-10-5' union allselect 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 allselect BB89237B-B43C-40D5-8D8E-1140492FB9F3,N'B',N'Old',N'2011-1-2' union allselect BB89237B-B43C-40D5-8D8E-1140492FB9F3,N'B',N'Old',N'2011-1-1' union allselect BB89237B-B43C-40D5-8D8E-1140492FB9F3,N'B',N'Per',N'2011-1-1' 希望得到结果：(按照ID和name分组，取分组中的New最小时间，和Old的最大时间)di    name      statusNew   statusOld       Newdate   OldDateBB89237B-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

SQL code

USE TEMPDBGOIF OBJECT_ID('#T') IS NOT NULL DROP TABLE #TGOCreate table #T([ID] uniqueidentifier,[Name] nvarchar(100),[Status] nvarchar(200),[Date] Datetime)Insert #Tselect 'BB89237B-B43C-40D5-8D8E-1140492FB9F4',N'A',N'New',N'2011-10-1' union allselect 'BB89237B-B43C-40D5-8D8E-1140492FB9F4',N'A',N'New',N'2011-10-2' union allselect 'BB89237B-B43C-40D5-8D8E-1140492FB9F4',N'A',N'Old',N'2011-10-1' union allselect 'BB89237B-B43C-40D5-8D8E-1140492FB9F4',N'A',N'Old',N'2011-10-5' union allselect '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 allselect 'BB89237B-B43C-40D5-8D8E-1140492FB9F3',N'B',N'Old',N'2011-1-2' union allselect 'BB89237B-B43C-40D5-8D8E-1140492FB9F3',N'B',N'Old',N'2011-1-1' union allselect 'BB89237B-B43C-40D5-8D8E-1140492FB9F3',N'B',N'Per',N'2011-1-1' GOSELECT 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 #TGROUP BY ID,NAME/*ID    NAME    NEW_MIN    OLD_MAXBB89237B-B43C-40D5-8D8E-1140492FB9F4    A    2011-10-01 00:00:00.000    2011-10-05 00:00:00.000BB89237B-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……