嗨,我有两张桌子
Student -------- Id Name 1 John 2 David 3 Will Grade --------- Student_id Mark 1 A 2 B 2 B+ 3 C 3 A
是否可以使本机Postgresql选择获得这样的结果:
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
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,你的don’t need to repeat列的SELECT到GROUP BY,例如。您不需要在GROUP BY上重复学生姓名。您只能在主键上GROUP BY。如果删除学生的主键,则需要在GROUP BY上重复学生姓名。
CREATE TABLE grade (Student_id int,Mark varchar(2)); INSERT INTO grade (Student_id,Mark) VALUES (1,'A'),(2,'B'),'B+'),(3,'C'),'A'); CREATE TABLE student (Id int primary key,Name varchar(5)); INSERT INTO student (Id,Name) VALUES (1,'John'),'David'),'Will');