我有一张桌子.
ID Date Value 1 12/12/2014 A 1 24/12/2014 T 2 13/12/2014 A 2 23/12/2014 T 3 12/03/2014 A 3 12/04/2014 T 4 12/12/2014 T 5 12/04/2014 T
我想要结果,如ADate是值为A的日期,而TDate是值为T的日期
ID ADate TDate 1 12/12/2014 24/12/2014 2 13/12/2014 23/12/2014 3 12/03/2014 12/04/2014 4 - 12/12/2014 5 - 12/04/2014
解决方法
使用条件聚合.试试这个
SELECT id,Min(CASE
WHEN value = 'A' THEN [Date]
END) Adate,Max(CASE
WHEN value = 'T' THEN [Date]
END) Tdate
FROM Tablename
GROUP BY id
更新：获取具有相同ID的行
DECLARE @cnt INT
SELECT TOP 1 @cnt = Count(1) / 2
FROM #test
GROUP BY id
ORDER BY Count(1) / 2 DESC
SELECT id,Max(CASE
WHEN value = 'T' THEN [Date]
END) Tdate
FROM (SELECT Row_number()
OVER (
partition BY id,value
ORDER BY date)%@cnt rn,*
FROM #test) a
GROUP BY id,rn
