sql - 使用 UNION 复制值

Question

我在下面有这个查询，但我在使用 UNION 时遇到了问题，它重复了 purok。

SELECT h.cpurok as purok,
COUNT(m.cgender) AS total,
SUM(CASE WHEN m.cgender::text = 'Male'::text THEN 1 ELSE 0 END) AS male, 
SUM(CASE WHEN m.cgender::text = 'Female'::text THEN 1 ELSE 0 END) AS female
FROM tbl_member as m, tbl_household as h, tbl_barangay as b, tbl_answer as a
WHERE h.chholdnumber=m.chholdnumber 
and h.cbrgycode=b.cbrgycode 
and b.cbrgyname='AGAO' 
and a.nqid=16 
and a.nmemberid=m.nmemberid
and choice in ('29','30','31','32','35','36')
GROUP BY purok 
UNION
SELECT h.cpurok as purok,
0 AS total, 0 AS male, 0 AS female
FROM tbl_household as h, tbl_answer as a
WHERE a.nqid=15
and choice='21'
order by purok

输出很好，只是想删除 total,male,female 中具有 0 值的重复 purok。

应删除上图中标记的红色。

Answer 1

Union 使行而不是单个键唯一。在您的情况下，您有两个不同的行 - 01, 2, 2, 0 和 01, 0, 0, 0，因此 UNION 保留他们两个。

您可能可以在表级别UNION ALL，将其包装在purok 上的大型GROUP BY 查询中:

SELECT purok,
SUM(num) AS total,
SUM(male) AS male, 
SUM(female) AS female
FROM (

    SELECT
        h.cpurok AS
    ,   1 AS num
    ,   CASE WHEN m.cgender::text = 'Male'::text THEN 1 ELSE 0 END AS male
    ,   CASE WHEN m.cgender::text = 'Female'::text THEN 1 ELSE 0 END AS female
    FROM tbl_member as m
    JOIN tbl_household as h ON h.chholdnumber=m.chholdnumber
    JOIN tbl_barangay as b ON h.cbrgycode=b.cbrgycode AND b.cbrgyname='AGAO'
    JOIN tbl_answer as a ON a.nmemberid=m.nmemberid AND a.nqid=16
    WHERE choice in ('29','30','31','32','35','36')

    UNION ALL

    SELECT h.cpurok as purok,
    0 AS num, 0 AS male, 0 AS female
    FROM tbl_household as h
    JOIN tbl_answer as a ON h.householdid=a.householdid AND a.nqid=15
    WHERE choice='21'
) raw
GROUP BY purok 
ORDER BY purok

注意:您的查询没有将 tbl_household 连接到 tbl_answer，所以我“发明”了一个占位符连接条件 h.householdid=a.householdid。您需要将其替换为实际的连接条件。