sql - 使用列在 SQL 中透视表并查找最大和最小日期
问题描述
我有以下数据集,其中包含三列类型、ID 和日期
Type ID Date
A input 2 20190108
A output 2 20190111
B input 6 20190213
A input 2 20190311
A output 2 20190319
B input 5 20190217
B output 6 20190215
C input 5 20190121
B input 6 20190609
B output 5 20190219
C input 5 20190112
C output 5 20190126
B input 6 20190121
B output 6 20190611
C input 2 20190101
B output 6 20190128
C output 2 20190105
C output 5 20190115
我需要旋转此表以获取每种类型的输入和输出的最大日期
Type ID Input date output date
A 2 20190311 20190319
B 6 20190609 20190611
C 5 20190217 20190219
我应该怎么做?我应该先旋转表格然后找到最大值吗?或相反亦然?
谢谢
解决方案
您似乎希望使用一些字符串处理逻辑进行聚合:
select substring_index(type, ' ', 1) as type, max(id) as id,
max(case when type like '% input' then date end) as input_date,
max(case when type like '% output' then date end) as output_date
from t
group by substring_index(type, ' ', 1);
编辑:
SQL Server 版本为:
select left(type, charindex(' ', type) - 1) as type, max(id) as id,
max(case when type like '% input' then date end) as input_date,
max(case when type like '% output' then date end) as output_date
from t
group by left(type, charindex(' ', type) - 1);