如何编写SQL查询计算运动员男女参赛人数的比值

方案1：基于你现有统计逻辑修改，保证计算口径一致 #

你可以通过条件聚合将分性别的统计结果行转列后直接计算比值，乘以1.0是为了避免数据库整数除法自动舍弃小数位：

SELECT 
    MAX(CASE WHEN sex = 'M' THEN total ELSE 0 END) * 1.0 / MAX(CASE WHEN sex = 'F' THEN total ELSE 0 END) AS male_to_female_ratio
FROM (
    SELECT 
        sex, SUM(total) AS total
    FROM (
        SELECT 
            games, sex, COUNT(sex) AS total
        FROM dbo.athlete_events$
        GROUP BY sex, games
    ) a
    GROUP BY sex
) gender_total

如果需要直接得到保留两位小数的2.64结果，可以嵌套ROUND函数：

SELECT 
    ROUND(MAX(CASE WHEN sex = 'M' THEN total ELSE 0 END) * 1.0 / MAX(CASE WHEN sex = 'F' THEN total ELSE 0 END), 2) AS male_to_female_ratio
FROM (
    SELECT 
        sex, SUM(total) AS total
    FROM (
        SELECT 
            games, sex, COUNT(sex) AS total
        FROM dbo.athlete_events$
        GROUP BY sex, games
    ) a
    GROUP BY sex
) gender_total

方案2：简化写法，去掉一层子查询 #

你也可以直接在内层分组的基础上做聚合计算，逻辑和上面完全一致：

SELECT 
    ROUND(SUM(CASE WHEN sex = 'M' THEN cnt ELSE 0 END) * 1.0 / SUM(CASE WHEN sex = 'F' THEN cnt ELSE 0 END), 2) AS male_to_female_ratio
FROM (
    SELECT games, sex, COUNT(sex) cnt
    FROM dbo.athlete_events$
    GROUP BY games, sex
) t

内容的提问来源于stack exchange，提问作者Nadavsh