mysql - 带有 LIMIT 和 GROUP BY 的 AVG

Question

我想做一个 SQL 查询，但我做不到...而且我找不到像我这样的例子。

我有一个简单的表格 People，有 3 列，7 条记录:

我想为每个团队获得 2 个最好的人的平均分。

我的查询:

SELECT team
     , (SELECT AVG(point) 
          FROM People t2 
         WHERE t1.team = t2.team 
         ORDER 
            BY point DESC 
         LIMIT 2) as avg 
  FROM People t1 
 GROUP 
    BY team

当前结果:(每个团队所有人的平均值)

显然，不可能在子查询中使用限制。 “ORDER BY point DESC LIMIT 2”被忽略。

预期结果:

我要的是每队最好的2人(最高分)的平均分，而不是每队所有人的平均分。

我该怎么做？如果有人有任何想法..

我在 MySQL 数据库上

fiddle 链接:http://sqlfiddle.com/#!9/8c80ef/1

谢谢!

Answer 1

你可以试试这个。

尝试通过子查询生成订单号，order by point desc.

然后每个团队只获得前 2 行，如果你想获得其他 top 号码只需修改 where 子句中的号码。

CREATE TABLE `People` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `team` varchar(20) NOT NULL,
  `point` int(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `People` (`id`, `name`, `team`, `point`) VALUES
(1, 'Luc', 'Jupiter', 10),
(2, 'Marie', 'Saturn', 0),
(3, 'Hubert', 'Saturn', 0),
(4, 'Albert', 'Jupiter', 50),
(5, 'Lucy', 'Jupiter', 50),
(6, 'William', 'Saturn', 20),
(7, 'Zeus', 'Saturn', 40);

ALTER TABLE `People`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `People`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

查询 1:

SELECT team,avg(point) totle
FROM People t1 
where (
      select count(*) 
      from People t2
      where t2.id >= t1.id and t1.team = t2.team
      order by t2.point desc
  ) <=2  ## if you want to get other `top` number just modify this number
group by team

Results :

|    team | totle |
|---------|-------|
| Jupiter |    50 |
|  Saturn |    30 |