MySQL GROUP_CONCT按另一列排序

Question

我遇到MySQL的"GROUP_ CONCT"；作用我将使用一个简单的测试数据库来说明我的问题：

publication :=
id, publication_id, title
-------------------------
1   1               foo
2   2               bar
authorships :=
id, publication_id, author_id, author_list_position
---------------------------------------------------
1   1               3          1
2   1               18         2
3   2               4          1
4   2               7          2
5   2               8          3
authors :=
id, author_id, name
-------------------
1   3          John
2   4          Jane
3   7          Jack
4   8          Chuck
5   18         Charles

这是DDL：

CREATE TABLE `test_publications` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`publication_id` int(11) DEFAULT NULL,
`title` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
CREATE TABLE `test_authorships` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`publication_id` int(11) DEFAULT NULL,
`author_id` int(11) DEFAULT NULL,
`author_list_position` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
CREATE TABLE `test_authors` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(11) DEFAULT NULL,
`name` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

INSERT INTO test_publications VALUES(1, 1, "foo");
INSERT INTO test_publications VALUES(2, 2, "bar");
INSERT INTO test_authors VALUES(1, 3, "John");
INSERT INTO test_authors VALUES(2, 4, "Jane");
INSERT INTO test_authors VALUES(3, 7, "Jack");
INSERT INTO test_authors VALUES(4, 8, "Chuck");
INSERT INTO test_authors VALUES(5, 18, "Charles");
INSERT INTO test_authorships VALUES(1, 1, 3, 1);
INSERT INTO test_authorships VALUES(2, 1, 18, 2);
INSERT INTO test_authorships VALUES(3, 2, 4, 1);
INSERT INTO test_authorships VALUES(4, 2, 7, 2);
INSERT INTO test_authorships VALUES(5, 2, 8, 3);

我想生成一个类似的视图

my_view :=
publication_id, authorlist, title

其中"authorlist"列应为相关出版物的所有作者的串联，按"authorships"排序author_list_position'，即

publication_id, title, authors 
----------------------------------------
1               foo    John, Charles
2               bar    Jack, Chuck, Jane

我试图使用GROUP_CONCT解决此问题，但未能正确排序：

SELECT id, title,
(SELECT GROUP_CONCAT(test_authors.name ORDER BY test_authorships.author_list_position SEPARATOR ', ')
FROM test_authors, test_authorships 
WHERE test_authorships.publication_id = test_publications.publication_id 
AND test_authorships.author_id = test_authors.author_id
) AS authorlist
FROM test_publications

id, title, authorlist
---------------------
1   foo    John, Charles
2   bar    Jane, Jack, Chuck

Rem. It should be "Jack, Chuck, Jane"

GROUP_COMPAT是正确的方式吗？谢谢

Answer 1

SELECT publication_id, 
test_publications.title, 
GROUP_CONCAT(test_authors.name ORDER BY test_authorships.author_list_position SEPARATOR ', ') authors 
FROM test_publications
JOIN test_authorships USING (publication_id)
JOIN test_authors USING (author_id)
GROUP BY publication_id, title

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=dbe0ea67b3c2bf5f442a6e68a51cfcef