我的两个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