PostgreSQL聚合数组 [英] Postgresql aggregate array

问题描述

我有两个桌子

Student
--------
Id  Name
1   John    
2   David
3   Will

Grade
---------
Student_id  Mark
1           A
2           B
2           B+
3           C
3           A

是否可以使本机Postgresql选择以得到如下结果：

Is it possible to make native Postgresql select to get results like this:

Name    Array of marks
-----------------------
'John',     {'A'}
'David',    {'B','B+'}
'Will',     {'C','A'}

但是不是这样

Name    Mark
----------------
'John',     'A'
'David',    'B'
'David',    'B+'
'Will',     'C'
'Will',     'A'

推荐答案

使用array_agg： http://www.sqlfiddle.com/#!1/5099e/1

Use array_agg: http://www.sqlfiddle.com/#!1/5099e/1

SELECT s.name,  array_agg(g.Mark) as marks        
FROM student s
LEFT JOIN Grade g ON g.Student_id = s.Id
GROUP BY s.Id

顺便说一句，如果您使用的是Postgres 9.1，则不需要重复 SELECT到GROUP BY上的列，例如您无需在GROUP BY上重复学生姓名。您只能在主键上使用GROUP BY。如果删除学生的主键，则需要在GROUP BY上重复学生的姓名。

By the way, if you are using Postgres 9.1, you don't need to repeat the columns on SELECT to GROUP BY, e.g. you don't need to repeat the student name on GROUP BY. You can merely GROUP BY on primary key. If you remove the primary key on student, you need to repeat the student name on GROUP BY.

CREATE TABLE grade
    (Student_id int, Mark varchar(2));

INSERT INTO grade
    (Student_id, Mark)
VALUES
    (1, 'A'),
    (2, 'B'),
    (2, 'B+'),
    (3, 'C'),
    (3, 'A');


CREATE TABLE student
    (Id int primary key, Name varchar(5));

INSERT INTO student
    (Id, Name)
VALUES
    (1, 'John'),
    (2, 'David'),
    (3, 'Will');

这篇关于PostgreSQL聚合数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！