聚合提供了一种直接的方法：

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);

我认为您的问题已通过此查询解决

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');

如果您的MySQL版本是8.0.X.X或更高，您可以使用CTE来实现您的目标！！
(Run此命令进行检查：）

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;