如果至少存在一个值 - SQL，则获取计数列

Question

如果分组依据子句中至少存在一个值，如何为每种颜色创建一个单独的列？ 每个颜色列是否需要一个单独的 select 语句，其中包含 group by 子句。 提前谢谢。

例如

SELECT
COUNT(ColorID) AS IcdCodecount,
Colorname
-- Blue -> if at least one value exists in 1, 10, 12
-- Red -> if at least one value in 0, 3, 4, 15
-- White -> if at least one value in  11, 12, 13, 14
-- Yellow -> if at least one value in 20, 21, 22, 23
FROM
TestTable
WHERE ColorID IN (
1, 10, 12, -- blue
0, 3, 4, 15, -- Red
11, 12, 13, 14, -- White
20, 21, 22, 23, -- yellow)
GROUP BY Colorname

Answer 1

您可以使用条件聚合：

(case when sum(case when colorId in (1, 10, 12) then 1 else 0 end)  > 0 then 1 else 0 end) as is_blue,
(case when sum(case when colorId in (0, 3, 4, 15) then 1 else 0 end)  > 0 then 1 else 0 end) as is_red,
(case when sum(case when colorId in (11, 12, 13, 14) then 1 else 0 end)  > 0 then 1 else 0 end) as is_white,
(case when sum(case when colorId in (20, 21, 22, 23) then 1 else 0 end)  > 0 then 1 else 0 end) as is_yellow

Answer 2

尝试使用数据透视表。Oracle 的实现描述如下：

https://www.oracletutorial.com/oracle-basics/oracle-pivot/