SQL新手求助：按等级统计枪支受欢迎程度并生成指定结果表

现有数据表（假设表名为your_table_name）：

需要生成结果表，按level分组，找出每个等级中出现次数最多的gun_name作为popular_gun_name：

支持窗口函数的数据库写法（MySQL 8+、PostgreSQL、SQL Server等） #

这种写法简洁高效，利用窗口函数实现排名：

WITH gun_counts AS (
    SELECT 
        level,
        gun_name,
        COUNT(*) AS count
    FROM 
        your_table_name
    GROUP BY 
        level, gun_name
),
ranked_guns AS (
    SELECT 
        level,
        gun_name,
        RANK() OVER (PARTITION BY level ORDER BY count DESC) AS rnk
    FROM 
        gun_counts
)
SELECT 
    level,
    gun_name AS popular_gun_name
FROM 
    ranked_guns
WHERE 
    rnk = 1;

逻辑说明： #

1. gun_counts CTE：按level和gun_name分组，统计每组的出现次数。
2. ranked_guns CTE：使用RANK()窗口函数，在每个level分组内按次数降序排名。
3. 最后筛选出排名为1的记录，得到每个等级的最受欢迎枪械名称。

不支持窗口函数的数据库写法（MySQL 5.x等） #

如果你的数据库版本较低，不支持窗口函数，可以使用嵌套查询：

SELECT 
    gc.level,
    gc.gun_name AS popular_gun_name
FROM 
    (SELECT 
         level, 
         gun_name, 
         COUNT(*) AS count
     FROM 
         your_table_name
     GROUP BY 
         level, gun_name) gc
INNER JOIN 
    (SELECT 
         level, 
         MAX(count) AS max_count
     FROM 
         (SELECT 
              level, 
              gun_name, 
              COUNT(*) AS count
          FROM 
              your_table_name
          GROUP BY 
              level, gun_name) gc2
     GROUP BY 
         level) max_counts 
ON gc.level = max_counts.level AND gc.count = max_counts.max_count;

逻辑说明： #

1. 内层子查询先统计每个level下各gun_name的出现次数。
2. 中间子查询找出每个level对应的最大出现次数。
3. 最后通过关联查询，匹配到每个level下次数等于最大次数的gun_name。

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