时间戳

首页 > 解决方案 > 在 PG Admin PostgreSQL 中合并 CASE 语句中的相似行

sql - 在 PG Admin PostgreSQL 中合并 CASE 语句中的相似行

问题描述

我的查询需要帮助。我有这个查询

SELECT DISTINCT("Fruits"), "Apple", "Orange", "Grapes", 
(SELECT (CASE WHEN "Apple" = TRUE 
         AND (("Orange" = TRUE OR "Orange" = FALSE OR "Orange" is NULL )
              OR ("Grapes" = TRUE OR "Grapes" = FALSE OR "Grapes" is NULL)) THEN ('1')
 ELSE (0)
 END) as "Market A"),
 (SELECT (CASE WHEN ("Apple" = FALSE OR "Apple" is NULL)
         AND (("Orange" = TRUE)
              AND ("Grapes" = FALSE OR "Grapes" is NULL)) THEN ('1')
 ELSE (0)
 END) as "Market B"),
 (SELECT (CASE WHEN ("Apple" = FALSE OR "Apple" is NULL)
         AND (("Orange" = TRUE)
              AND ("Grapes" = TRUE )) THEN ('1')
 ELSE (0)
 END) as "Market C")
 FROM market
      WHERE "Fresh" = TRUE
      GROUP BY "Fruits", "Apple", "Orange", "Grapes"

这是我得到的结果

-------------------------------------------------------------------
Market   | Apple | Orange | Grapes | Market A | Market B | Market C |
-------------------------------------------------------------------
Basket A | true  | true   | true   |    1     |    0     |    0     |
Basket A | true  | true   | null   |    1     |    0     |    0     |
Basket B | null  | null   | null   |    0     |    0     |    0     |
Basket A | true  | null   | null   |    1     |    0     |    0     |
Basket A | null  | true   | null   |    0     |    1     |    0     |
Basket C | null  | true   | null   |    0     |    1     |    0     |

我想要下表中的结果。寻求有关如何组合相似行并计算它们的帮助？非常感谢您的帮助并非常感谢。只是做一些查询的新手。

------------------------------------------------------------------
Market   | Market A | Market B | Market C |
-------------------------------------------------------------------
Basket A |    3     |    0     |    0     |
Basket A |    0     |    1     |    0     |
Basket C |    0     |    1     |    0     |

标签： sqlpostgresqlcase

解决方案

看来您正在寻找条件聚合：

select m.market, -- not sure where this comes from
       count(*) filter (where "Apple" = true) as "Market A",
       count(*) filter (where coalesce("Apple", false) = false 
                          and "Orange" 
                          and coalesce("Grape", false) = false) as "Market B",
       count(*) filter (where coalesce("Apple", false) = false 
                          and "Orange" = true
                          and "Grapes" = true) as "Market C"
from market m
where "Fresh" = true
group by m.market;

请注意，我删除了条件("Orange" = TRUE OR "Orange" = FALSE OR "Orange" is NULL )，("Grapes" = TRUE OR "Grapes" = FALSE OR "Grapes" is NULL)因为两者始终为真，并且不会改变您的条件的结果。

我不清楚market您想要的输出中的列是否不是表格的列。

推荐阅读