如何基于vb10_cartridgedata表创建NestID分组的FailCode计数表？

你的原SQL存在三个核心问题：

1. 分组时包含了FailCode，导致每个NestID会被拆分成多个行（每个不同的FailCode单独一行），不符合每个NestID一行的统计需求。
2. count("FailCode"=23)的写法错误：count会统计所有非空值，而"FailCode"=23返回的布尔值（true/false）都是非空，所以实际统计的是该分组的总行数，不是符合条件的记录数。
3. 条件中的FailCode值与需求不匹配：你需要统计FailCode=10（Ch1）和FailCode=23（Ch2），但原SQL写了23和11。

基础实现（仅统计存在数据的NestID） #

方法1：使用SUM+CASE表达式 #

SELECT 
  "NestID",
  SUM(CASE WHEN "FailCode" = 10 THEN 1 ELSE 0 END) AS "Ch1",
  SUM(CASE WHEN "FailCode" = 23 THEN 1 ELSE 0 END) AS "Ch2"
FROM public.vb10_cartridgedata
GROUP BY "NestID"
ORDER BY "NestID";

方法2：使用COUNT+FILTER子句（PostgreSQL专属简洁写法） #

SELECT 
  "NestID",
  COUNT(*) FILTER (WHERE "FailCode" = 10) AS "Ch1",
  COUNT(*) FILTER (WHERE "FailCode" = 23) AS "Ch2"
FROM public.vb10_cartridgedata
GROUP BY "NestID"
ORDER BY "NestID";

完整实现（确保1-16所有NestID都显示，空值显示0） #

如果需要强制显示1到16的所有NestID（即使该NestID没有任何数据），可以生成一个包含1-16的序列表，再与原表左连接：

WITH nest_ids AS (
  SELECT generate_series(1, 16) AS "NestID"
)
SELECT 
  n."NestID",
  COALESCE(SUM(CASE WHEN c."FailCode" = 10 THEN 1 ELSE 0 END), 0) AS "Ch1",
  COALESCE(SUM(CASE WHEN c."FailCode" = 23 THEN 1 ELSE 0 END), 0) AS "Ch2"
FROM nest_ids n
LEFT JOIN public.vb10_cartridgedata c ON n."NestID" = c."NestID"
GROUP BY n."NestID"
ORDER BY n."NestID";

说明 #

• generate_series(1,16)生成1到16的连续整数，确保所有NestID都被覆盖。
• LEFT JOIN保留所有NestID，即使原表中没有对应数据。
• COALESCE函数将统计结果的空值转换为0，满足空值显示0的要求。

内容的提问来源于stack exchange，提问作者Lalo Quera