mysql - SQL查询和子查询

Question

我有一个问题要问所有 SQL 专家。

有一个表格看起来像:

 clanname  country     points
 name1     country1    100
 name2     country1    90
 name3     country1    10
 name4     country2    100
 name5     country2    80
 name6     country2    70

我想制作一个仅涉及每个国家/地区前 2 名结果的国家/地区排名。所以，在这个例子中，排名应该是:

 country    average-points
 country2   95
 country1   90

是否有一种方法可以使用子查询仅通过一个 SQL 查询来获取此结果？

实际上，我有 200 多个国家/地区......每个国家/地区都有数千个结果。但我只想过滤每个国家/地区的前 30 个结果。

现在我设法使用此查询获取一个国家/地区的平均值:

  SELECT 
      location, AVG(warswon)
  FROM
      (SELECT 
           `name`, `location`, `warswon` 
       FROM 
           `clans`
       WHERE 
           location = 'China'
       ORDER BY 
           `clans`.`warswon` DESC 
       LIMIT 30) AS top30ofcountry

但是如何在一次查询中获得每个国家/地区的平均结果？

这可能吗？

Answer 1

这是每组 n 的问题。这在许多使用 row_number 的数据库中是微不足道的。在 MySQL 中，您可以使用用户变量来做到这一点

SELECT 
  country ,
  avg(points)
FROM
(
  SELECT 
        @num := if(@group = `country`, @num + 1, 1) as row_number,
        @group := `country` as dummy,
        clanName,
        country,
        points
    FROM
      clans
      JOIN (SELECT @group := NULL, @num := 0) as z
     order by
        country, points desc) as x 
WHERE x.row_number <=2 
GROUP BY 
    country