PostgreSQL PG Admin中CASE语句查询结果相似行合并及计数求助

API 探路者

2026-10-5

核心调整思路

精简原有冗余的判断逻辑：你原查询中Market A的判断里，关于Orange和Grapes的条件覆盖了所有可能的取值（真/假/空），属于永远成立的冗余条件，可以直接删除简化判断。
嵌套子查询做二次聚合：你原来的GROUP BY携带了Apple、Orange、Grapes三个字段，所以每一种不同的取值都会拆成单独的行。我们先基于原有逻辑计算每行的三个市场标记，再按市场名称和三个标记值分组，对标记做求和统计，同时过滤掉三个标记全为0的无效行，即可得到合并后的计数结果。

调整后完整查询

SELECT 
  "Fruits" AS "Market",
  SUM("Market A") AS "Market A",
  SUM("Market B") AS "Market B",
  SUM("Market C") AS "Market C"
FROM (
  SELECT 
    "Fruits",
    -- 原逻辑简化：Orange和Grapes的条件永远成立，仅判断Apple即可
    CASE WHEN "Apple" = TRUE THEN 1 ELSE 0 END AS "Market A",
    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",
    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
) AS t
-- 过滤掉三个标记全为0的无效行
WHERE "Market A" + "Market B" + "Market C" > 0
GROUP BY "Fruits", "Market A", "Market B", "Market C"
ORDER BY "Fruits";