如何按y列获得x列GROUPed的值列表？

Question

我有以下表结构和数据(使用Oracle DB 12c)：

CREATE TABLE authors (
    aid NUMBER(38) GENERATED ALWAYS AS IDENTITY CONSTRAINT authors_pk PRIMARY KEY NOT NULL,
    fname VARCHAR2(100 CHAR) NOT NULL,
    sname VARCHAR2(150 CHAR) NOT NULL,
    dob NUMBER(4),
    gender CHAR(1 CHAR)
);

CREATE UNIQUE INDEX authors_uind ON authors (fname, sname, dob, gender); --- Combination of columns must be unique

INSERT INTO authors(sname, fname, dob, gender) VALUES('Codd', 'Edgar F', 1923, 'M');
INSERT INTO authors(sname, fname, dob, gender) VALUES('Date', 'Chris J', 1941, 'M');
INSERT INTO authors(sname, fname, dob, gender) VALUES('Darwin', 'Hugh', 1943, 'M');
INSERT INTO authors(sname, fname, dob, gender) VALUES('Lions', 'John', 1937, 'M');

CREATE TABLE publications (
    pid NUMBER(38) GENERATED ALWAYS AS IDENTITY CONSTRAINT publications_pk PRIMARY KEY NOT NULL,
    title VARCHAR2(150 CHAR) NOT NULL,
    written NUMBER(4)
);
CREATE INDEX publications_ind ON publications (title, written);

INSERT INTO publications(title, written) VALUES('A Relational Model of Data for Large Shared Data Banks', 1970);
INSERT INTO publications(title, written) VALUES('The Relational Model for Database Management', 1990);
INSERT INTO publications(title, written) VALUES('An Introduction to Database Systems', 2003);
INSERT INTO publications(title, written) VALUES('The Third Manifesto', 2000);
INSERT INTO publications(title, written) VALUES('Temporal Data and the Relational Model', 2002);
INSERT INTO publications(title, written) VALUES('Database in Depth: Relational Theory for Practitioners', 2005);
INSERT INTO publications(title, written) VALUES('Commentary on UNIX', 1976);

CREATE TABLE author_publications (
    aid NUMBER(38) REFERENCES authors (aid),
    pid NUMBER(38) REFERENCES publications (pid),
    CONSTRAINT author_publications_pk PRIMARY KEY (aid, pid)
);

INSERT INTO author_publications(aid, pid) VALUES(1, 1);
INSERT INTO author_publications(aid, pid) VALUES(1, 2);
INSERT INTO author_publications(aid, pid) VALUES(2, 3);
INSERT INTO author_publications(aid, pid) VALUES(2, 4);
INSERT INTO author_publications(aid, pid) VALUES(2, 5);
INSERT INTO author_publications(aid, pid) VALUES(2, 6);
INSERT INTO author_publications(aid, pid) VALUES(3, 4);
INSERT INTO author_publications(aid, pid) VALUES(3, 5);
INSERT INTO author_publications(aid, pid) VALUES(4, 7);

一个作者写了多本书，一本书是由多位作者写的，这是可能的。

查询应该是什么样的，这样我就可以得到每本书的一行(对于所有的书)，并将作者的名字连在一起。例：

| Written | Title | PID | Authors |
|---------|-------|-----|---------|
| 1990 | THE THIRD MANIFESTO | 4 | DATE, DARWIN |
| 2002 | TEMPORAL DATA AND THE RELATIONAL MODEL | 8 | DARWIN, DATE |

注1:第四列中作者姓名的顺序不重要，行中单个作者的名字后面的逗号也不重要。

注2:如果可以通过不同的方法实现所需的结果，则不需要使用GROUP BY子句。

我的查询如下：

--- Get a list of author names per title
SELECT p.written, upper(p.title), p.pid, concat(upper(a.sname), ', ') 
FROM publications p INNER JOIN author_publications apub on p.pid = apub.pid
INNER JOIN authors a on apub.aid = a.aid
GROUP BY p.pid
order by p.written, upper(p.title)
;

我得到的错误消息是“不是按表达式分组”。(我不明白那句话的哪一部分失败了，也不明白为什么。)所讨论的行是SELECT语句的开始。

Answer 1

而不是使用CONCAT，而是使用LISTAGG：

select
  p.written, upper(p.title) as title, p.pid,
  upper(listagg(a.sname, ', ') within group (order by a.sname)) as authors
from publications p
join author_publications ap on p.pid = ap.pid
join authors a on ap.aid = a.aid
group by p.written, p.title, p.pid
order by p.written;

结果：

WRITTEN TITLE                                                         PID AUTHORS
------- ------------------------------------------------------------ ---- ---------------
   1970 A RELATIONAL MODEL OF DATA FOR LARGE SHARED DATA BANKS          1 CODD
   1976 COMMENTARY ON UNIX                                              7 LIONS
   1990 THE RELATIONAL MODEL FOR DATABASE MANAGEMENT                    2 CODD
   2000 THE THIRD MANIFESTO                                             4 DARWIN, DATE
   2002 TEMPORAL DATA AND THE RELATIONAL MODEL                          5 DARWIN, DATE
   2003 AN INTRODUCTION TO DATABASE SYSTEMS                             3 DATE
   2005 DATABASE IN DEPTH: RELATIONAL THEORY FOR PRACTITIONERS          6 DATE