Я хочу отобразить сводку, подобную этой, на основе трех таблиц
Мои 3 таблицы MYSQL выглядят следующим образом:
Таблица 1: гражданин
=============================
ID | Name | Sex | Address |
=============================
5 | James | Male | India
6 | Shella|Female | India
7 | Jan | Male | NY
8 | May | Female | USA
==============================
Таблица 2: преимущества
==========================
ID| benefits
==========================
1 | SSS
2 | Coco Life
3 | PhiHealth
4 | Sunlife
==========================
Таблица 3: пенсионеры
============================
ID| benefits_ID | citizen_ID
============================
1 | 1 | 5
2 | 2 | 6
3 | 1 | 7
4 | 4 | 7
==========================
Я хочу отобразить это выглядит так:
====================================================================
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 |
==================================================================
Кто-нибудь может дать мне подсказку о том, как это сделать?
Решение
Вы можете сделать Left Join от Address стол к benefits стол, через pensioners таблица, используя соответствующие отношения. Левое объединение позволяет нам рассмотреть 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 pensioners AS p
ON p.citizen_ID = c.ID
LEFT JOIN benefits AS b
ON b.ID = p.benefits_ID
GROUP BY c.Address
Другие решения
Других решений пока нет …
detector