sqlserver 取每个时间段多行多列的最大值
问题描述：
ID Time A B C
1 2000-01-01 10:10:10 20 30 15
2 2000-01-01 11:20:20 30 15 40
3 2000-01-01 12:30:30 30 20 10
4 2000-01-02 10:10:10 30 40 25
5 2000-01-02 11:20:20 20 50 60
表格就像上面一样，现在需要求时间段内A、B、C的最大值
结果像
Time max
2000-01-01 11:20:20 40
2000-01-02 11:20:20 60
答
select convert(char(10),time,120) 日期,
(case when MAX(A)>MAX(B) and MAX(A)>MAX(C) then MAX(A)
when MAX(B)>MAX(A) and MAX(B)>MAX(C) then MAX(B)
when MAX(C)>MAX(A) and MAX(C)>MAX(B) then MAX(C)
end)ABC最大值
from ttt group by convert(char(10),time,120)
select * from ttt
答
SELECT CASE WHEN max(a) > max(b) and max(a) > max(c) THEN max(a) WHEN max(b) > max(a) and max(b) > max(c) THEN max(b) ELSE max(c) END as max FROM table group by datepart(day, time)