时间戳

首页 > 解决方案 > 如何在 SQL 中获得每个幼儿园最后三个成绩的平均值？

sql - 如何在 SQL 中获得每个幼儿园最后三个成绩的平均值？

问题描述

我在 SQL 中有两个表。第一个有幼儿园的名称和他们的 ID。第二个表格包括年级 ID、幼儿园 ID、他们的年级和评分日期。我只需要获得每个幼儿园最后三个成绩的平均值。

这些是我的表：

幼儿园|kindergarden_id|kindergarden_name|kindergarden_address|kindergarden_user_id|

kindergarden_grade |grade_id|grade|grade_date|kindergarden_kindergarden_id|

我目前正在尝试这个，通过这个查询，我得到了我所有成绩的平均值，而不是最后三个。

SELECT r.kindergarden_name, AVG(r.grade)FROM (SELECT kindergarden.kindergarden_name, kindergarden_grade.grade from kindergarden left join kindergarden_grade on kindergarden.kindergarden_id=kindergarden_grade.kindergarden_kindergarden_id order by kindergarden_grade.grade_date)as r group by r.kindergarden_name

这是我得到的，这是幼儿园和他们所有成绩的平均值：

柯基犬 3.0000 蝴蝶 4.0000 想象力 2.0000

标签： sql

解决方案

您可以使用窗口函数来获得最后三个成绩：

SELECT k.kindergarden_name, AVG(r.grade)
FROM (SELECT k.kindergarden_name, kg.grade,
             ROW_NUMBER() OVER (PARTITION BY k.kindergarden_name ORDER BY kg.grade_date) as seqnum
      FROM kindergarden k LEFT JOIN
           kindergarden_grade kg
           ON k.kindergarden_id = kg.kindergarden_kindergarden_id 
    ) k
WHERE seqnum <= 3
GROUP BY k.kindergarden_name;

编辑：

旧版本的 MySQL 不支持ROW_NUMBER()。有多种解决方案，但一种是：

SELECT k.kindergarden_name, AVG(r.grade)
FROM kindergarden k LEFT JOIN
     kindergarden_grade kg
     ON k.kindergarden_id = kg.kindergarden_kindergarden_id 
WHERE kg.grade_date >= ANY (SELECT kg2.grade_date
                            FROM kindergarden_grade kg2
                            WHERE kg2.kindergarden_kindergarden_id = kg.kindergarden_kindergarden_id
                            ORDER BY kg2.grade_date DESC
                            LIMIT 1 OFFSET 2
                           )
 GROUP BY k.kindergarden_name;

这使用子查询返回第三个最新日期。ANY处理特定幼儿园没有三个日期的情况。

推荐阅读