sql

krugob8w 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(181)

如果GROUPBY子句中至少存在一个值，如何为每种颜色创建单独的列？是否每个颜色列都需要一个带有GROUPBY子句的单独select语句。提前谢谢。
例如

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

sql

来源：https://stackoverflow.com/questions/62461705/get-count-column-if-at-least-one-value-exist-sql

2条答案

按热度按时间

qv7cva1a1#

可以使用条件聚合：

(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

赞(0）回复(0）举报 2021-07-26

4szc88ey2#

尝试使用数据透视表。oracle的实现描述如下：
https://www.oracletutorial.com/oracle-basics/oracle-pivot/

赞(0）回复(0）举报 2021-07-26