顯示被確定為“M”的患者多于“F”的省份。只能顯示完整的省名
患者表：患者 ID INT first_name TEXT last_name TEXT 性別 CHAR(1)birth_date DATE 城市 TEXT 省 ID CHAR(2) 過敏 TEXT 身高 INT 體重 INT
Province_names 表： province_id CHAR(2) Province_name TEXT
我試過這個，但它不作業。
`select province_name
from patients p,province_names pn
where p.province_id = pn.province_id
and (count(p.province_id) group by p.patient_id having p.gender = 'M' ) >
(count(p.province_id) group by p.patient_id having p.gender = 'F') `
uj5u.com熱心網友回復：
聚合提供了一種簡單的方法：
SELECT pn.province_name
FROM province_names pn
INNER JOIN patients p
ON p.province_id = pn.province_id
GROUP BY pn.province_name
HAVING COUNT(CASE WHEN p.gender = 'M' THEN 1 END) >
COUNT(CASE WHEN p.gender = 'F' THEN 1 END);
uj5u.com熱心網友回復：
我認為您的問題已通過此查詢解決
select res.province_name
from (SELECT pn.province_name,
case when p.gender = 'M' then 1 else 0 end as gender_no,
count(*) as cnt
FROM province_names pn
INNER JOIN patients p
ON p.province_id = pn.province_id
group by pn.province_names, p.GENDER) res
where res.gender_no = 1
and cnt > (select count(*)
from province_names pnn
INNER JOIN patients pp
ON pp.province_id = pnn.province_id
where res.province_name = pnn.province_name
and pp.GENDER = 'F');
uj5u.com熱心網友回復：
如果您的 MySQL 版本是 8.0.XX 或更高版本，您可以使用 CTE 來實作您的目標！
（運行此命令進行檢查：）
mysql> SELECT VERSION();
-----------
| VERSION() |
-----------
| 8.0.28 |
-----------
1 row in set (0.00 sec)
完整代碼：
WITH CTE AS (SELECT province_name, COUNT(*) AS MaleCount FROM patients p JOIN province_names pn ON p.province_id = pn.province_id WHERE gender = 'M' GROUP BY province_name)
, CTE2 AS (SELECT province_name, COUNT(*) AS FemaleCount FROM patients p JOIN province_names pn ON p.province_id = pn.province_id WHERE gender = 'F' GROUP BY province_name)
SELECT CTE.province_name FROM CTE JOIN CTE2 ON CTE.province_name = CTE2.province_name WHERE MaleCount > FemaleCount;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/521040.html
標籤：mysql