如何用SQL查询各国板球击球手中得分距该国榜首差不超500分的人数

实现思路 #

• 先计算每个国家的榜首最高得分
• 将最高得分关联到对应国家的所有击球手记录中
• 筛选出得分与榜首分差≤500的击球手，按国家分组统计人数

写法1：窗口函数实现（兼容MySQL 8.0+/PostgreSQL/SQL Server等主流新版本数据库） #

这是效率最高、最简洁的写法，无需多次关联原表：

SELECT 
    country,
    COUNT(*) AS qualified_batsman_count
FROM (
    SELECT 
        country,
        runs,
        -- 窗口函数计算每个国家的最高得分
        MAX(runs) OVER (PARTITION BY country) AS country_top_runs
    FROM batsman_scores -- 替换为你的实际表名
) AS country_runs_info
WHERE runs >= country_top_runs - 500
GROUP BY country;

写法2：子查询关联实现（兼容不支持窗口函数的低版本数据库） #

如果你的数据库版本较老不支持窗口函数，可以用分组子查询先拿各国最高分再关联：

SELECT 
    country_max.country,
    COUNT(batsman.name) AS qualified_batsman_count
FROM (
    -- 先查询每个国家的榜首得分
    SELECT country, MAX(runs) AS top_runs
    FROM batsman_scores -- 替换为你的实际表名
    GROUP BY country
) AS country_max
LEFT JOIN batsman_scores AS batsman
    ON batsman.country = country_max.country
    AND batsman.runs >= country_max.top_runs - 500
GROUP BY country_max.country;

补充说明 #

• 如果你需要统计所有国家的符合要求总人数，而非分国家统计，只需要去掉外层查询的GROUP BY country语句，直接返回COUNT(*)即可
• 筛选逻辑和示例完全匹配：若印度榜首得分为1500，1500-500=1000，会自动筛选得分≥1000的印度击球手

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