mariadb SQL中具有多个group by子句的SUM和MAX函数导致问题

vxbzzdmp  于 2022-12-23  发布在  其他
关注(0)|答案(3)|浏览(134)

我有下表:
| 身份证|学生|周期|点|
| - ------|- ------|- ------|- ------|
| 1个|1个|第一季度|无|
| 第二章|第二章|第一季度|第二章|
| 三个|第二章|第二季度|五个|
| 四个|第二章|第三季度|无|
| 五个|三个|第一季度|七|
| 六个|三个|第一季度|八个|
| 七|三个|第二季度|三个|
| 八个|三个|第二季度|1个|
| 九|三个|第三季度|无|
| 十个|三个|第三季度|无|
| 十一|四个|第一季度|1个|
| 十二|四个|第三季度|九|
我想知道哪个学生在哪一段时间的总分最多。
当我执行这个查询时:

SELECT
    MAX(SUM(point)) score,
    student,
    `period`
FROM table1
GROUP BY student, `period`

它给出以下错误:

#1111 - Invalid use of group function

当我执行这个查询时:

SELECT
    `period`,
    student,
    MAX(p) score
FROM
(
    SELECT
        SUM(point) p,
        student,
        `period`
    FROM table1
    GROUP BY student, `period`
) t1
GROUP BY `period`

它给出以下结果:
| 周期|学生|刻痕|
| - ------|- ------|- ------|
| 第一季度|1个|十五|
| 第二季度|1个|五个|
| 第三季度|1个|九|
期间和他们的最高点是好的,但我总是有第一个学生证。

预期产出:

| 周期|学生|刻痕|
| - ------|- ------|- ------|
| 第一季度|三个|十五|
| 第二季度|第二章|五个|
| 第三季度|四个|九|
最重要的是,如果有不止一个学生的分数最高,我想知道他们所有人。

hfyxw5xn

hfyxw5xn1#

您可以按如下方式使用最大窗口函数:

WITH sum_pt AS
(
    SELECT student, period,
    SUM(point) AS st_period_pt
    FROM table1
    GROUP BY student, period
),
max_sum as
(
  SELECT *,
    MAX(st_period_pt) OVER (PARTITION BY period) AS max_pt_sum
  FROM sum_pt
)
SELECT student, period,  st_period_pt
FROM max_sum
WHERE st_period_pt = max_pt_sum
ORDER BY period

参见demo

sbtkgmzw

sbtkgmzw2#

尝试使用窗口函数:

  • SUM,以获得每对〈student,period〉的总积分
  • ROW_NUMBER,对每个周期的点数进行排序

然后,您可以选择排名= 1,以获得每个时段的最高分。

WITH students_with_total_points AS (
    SELECT *, SUM(point) OVER(PARTITION BY student, period) AS total_points 
    FROM tab
), ranking_on_periods AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY period ORDER BY total_points DESC) AS rn 
    FROM students_with_total_points
)
SELECT id, student, period, total_points
FROM ranking_on_period
WHERE rn = 1
ercv8c1e

ercv8c1e3#

可以按如下方式使用左联接:

select t1.period, t1.student, t1.score
from (
    select student, period, score
    from (
        select student, period, SUM(point) as score 
        from table1 s
        group by student, period
    ) as s
    group by period, student
) as t1
left join (
    select student, period, score
    from (
        select student, period, SUM(point) as score 
        from table1 s
        group by student, period
    ) as s
    group by period, student
) as t2 on t1.student = t2.student and t1.score < t2.score
where t2.score is null;

这个查询还将列出学生和他们的时间段,如果分数为0,您可以通过在t1和t2临时表中添加where close来排除他们。

相关问题