我的两个MYSQL表如下:
表1:公民

=============================
ID |  Name | Sex    | Address |
=============================
5  | James | Male   | India
6  | Shella|Female  | India
7  | Jan   | Male   | NY
8  | May   | Female | USA
==============================

表2:效益
==========================
ID| citizen_ID | benefits
==========================
1 | 5          | SSS
2 | 6          | Coco Life
3 | 7          | SSS
4 | 7          | Sunlife
==========================

我想显示如下:
====================================================================
Address | Total Citizen | Male | Female | SSS | Coco Life | Others |
====================================================================
India   | 2             |  1   |  1     |  1  |    1      |   0    |
NY      | 1             |  1   |  0     |  1  |    0      |   1    |
USA     | 1             |  0   |  1     |  0  |    0      |   0    |
==================================================================

有人能告诉我怎么做吗?
我的初始代码:
        $sql = "SELECT  Address,
            count(case when Sex='Male' then 1 end) as male_cnt,
            count(case when Sex='Female' then 1 end) as female_cnt,
            count(*) as total_cnt FROM citizen
            GROUP BY Address";

最佳答案

你在正确的轨道上。现在只需要从Left Join表到Address表执行benefits操作。Left join允许我们考虑一个Address条目,即使它的任何公民都没有相应的benefits条目。
为了计算公民总数、男性人数和女性人数,您现在需要在加入后使用COUNT(DISTINCT ID)。因为连接可能会创建重复的行,因为一个公民可能有多个好处。
此外,为了计算“其他”好处,我们需要确保benefit IS NOT NULL和它是NOT IN ('SSS', 'Coco Life')
在多表查询中,建议使用Aliasing以提高代码的清晰度(可读性)并避免不明确的行为。

SELECT
  c.Address,
  COUNT(DISTINCT CASE WHEN c.Sex = 'Male' THEN c.ID END) AS male_cnt,
  COUNT(DISTINCT CASE WHEN c.Sex = 'Female' THEN c.ID END) AS female_cnt,
  COUNT(DISTINCT c.ID) AS total_citizen_cnt,
  COUNT(CASE WHEN b.benefits = 'SSS' THEN 1 END) AS SSS_cnt,
  COUNT(CASE WHEN b.benefits = 'Coco Life' THEN 1 END) AS Coco_Life_cnt,
  COUNT(CASE WHEN b.benefits IS NOT NULL AND
                  b.benefits NOT IN ('SSS', 'Coco Life') THEN 1 END) AS Others_cnt
FROM citizen AS c
LEFT JOIN benefits AS b
  ON b.citizen_ID = c.ID
GROUP BY c.Address

10-07 19:57
查看更多