MySQL 统计性别实例

Question

我有以下 MySQL 查询。

USE demo;
SELECT 
  loc.name,
  sum(case cr_sex when 'F' then 1 else 0 end) as 'F#', 
  sum(case cr_sex when 'M' then 1 else 0 end) as 'M#'
FROM case_report_main as cr 
JOIN location as loc on cr.cr_name = loc.name
GROUP BY loc.name

这按照我想要的方式工作，它计算每种性别的每次出现并按位置名称对其进行分组。但是当我尝试将它与另一张表连接时

USE demo;
SELECT 
  loc.name,
  sum(case cr_sex when 'F' then 1 else 0 end) as 'F#', 
  sum(case cr_sex when 'M' then 1 else 0 end) as 'M#'
FROM case_report_main as cr 
JOIN location as loc on cr.cr_name=loc.name
JOIN map_polygons as mp on mp.polygon_name=loc.name
GROUP BY loc.name

值完全困惑，返回数千而不是预期的数十和数百。

我认为 我理解这个问题，额外的表增加了行数。我不确定如何解决这个问题，或者这是否真的是问题所在。

Answer 1

因为可能有记录在表 map_polygons 上有多个匹配导致对性别的计数无效，尝试将第一个查询放在子查询中，

SELECT  mp.*,
        loc.name
        loc.`F#`
        loc.`M#`
FROM
        (
            SELECT  loc.name,
                    sum(case cr_sex when 'F' then 1 else 0 end) as 'F#', 
                    sum(case cr_sex when 'M' then 1 else 0 end) as 'M#'
            FROM case_report_main as cr 
            JOIN location as loc on cr.cr_name = loc.name
            GROUP BY loc.name
        ) loc
        INNER JOIN map_polygons mp on mp.polygon_name=loc.name 

顺便说一句，因为是mysql，所以可以对boolean结果求和，使其更短

SELECT  mp.*,
        loc.name
        loc.`F#`
        loc.`M#`
FROM
        (
            SELECT  loc.name,
                    sum(cr_sex = 'F') as 'F#', 
                    sum(cr_sex = 'M') as 'M#'
            FROM case_report_main as cr 
            JOIN location as loc on cr.cr_name = loc.name
            GROUP BY loc.name
        ) loc
        INNER JOIN map_polygons mp on mp.polygon_name=loc.name