mysql - SQL 垂直分组

Question

我有以下 RDB 表:

ID  Feature
1   1
1   2
2   1
3   1
3   2
3   3

我想要的是下面的输出:

ID  Feature1    Feature2    Feature3
1   true        true        false
2   true        false       false
3   true        true        true

实现此目的最简单的 SQL 查询是什么？

Answer 1

使用cross join 和left join 将原始表获取所有 id 和所有功能组合以获得所需的结果。

select i.id,
max(case when f.feature=1 and t.feature is not null then 'true' else 'false' end) as feature1,
max(case when f.feature=2 and t.feature is not null then 'true' else 'false' end) as feature2,
max(case when f.feature=3 and t.feature is not null then 'true' else 'false' end) as feature3
from (select distinct feature from t) f --replace this with the feature table if you have one
cross join (select distinct id from t) i
left join t on t.id=i.id and t.feature=f.feature
group by i.id

如果您只需要 bool 值 1,0 表示 True,False，则查询可以简化为

select i.id,
max(f.feature=1 and t.feature is not null) as feature1,
max(f.feature=2 and t.feature is not null) as feature2,
max(f.feature=3 and t.feature is not null) as feature3
from (select distinct feature from t) f --replace this with the feature table if you have one
cross join (select distinct id from t) i
left join t on t.id=i.id and t.feature=f.feature
group by i.id