gpt4 book ai didi

SQL group by 列出每个名称的所有值

转载 作者:行者123 更新时间:2023-11-29 14:09:31 25 4
gpt4 key购买 nike

我正在努力避免重复学生的名字。我对 SQL 查询还很陌生。只是想知道是否有更好的方法通过将每个学生的所有内容列在一行中来做到这一点。

SELECT s.name students, c.name classes 
FROM students s INNER JOIN enrollments e ON s.id=e.student_id
INNER JOIN classes c ON e.class_id=c.id
GROUP BY s.name, c.name;

给我

students  |    classes
-----------+----------------
Paige | Calculus
Pajak | Calculus
Pajak | Yoga
Pamela | Calculus
Pamela | Cooking Pasta
Pamela | Football
Pamela | Singing
Pamela | Yoga
Parker | English
Parker | Fruit
Parker | Social Studies

只是好奇是否有更简洁的方法。

最佳答案

也许你想要listagg():

SELECT s.name as students, listagg(c.name) within group(order by c.name) as classes 
FROM students s INNER JOIN
enrollments e
ON s.id = e.student_id INNER JOIN
classes c
ON e.class_id = c.id
GROUP BY s.name, c.name;

这将创建每个学生所在类(class)的逗号分隔列表。

编辑:

在 Postgres 中,您可以使用 string_agg():

SELECT s.name as students, string_agg(c.name order by c.name) as classes 
FROM students s INNER JOIN
enrollments e
ON s.id = e.student_id INNER JOIN
classes c
ON e.class_id = c.id
GROUP BY s.name, c.name;

关于SQL group by 列出每个名称的所有值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/46411840/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com