mysql - MySQL GROUP_CONCAT 按另一列排序

问题描述

我遇到了 MySQL 的“GROUP_CONCAT”函数的问题。我将使用一个简单的测试数据库来说明我的问题：

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

其中“作者列表”列应该是相关出版物的所有作者的串联，按照“作者身份”排序。“作者列表位置”，即

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

我尝试使用 GROUP_CONCAT 解决此问题，但未能正确订购：

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_CONCAT 是正确的方式吗？谢谢！

标签： mysql

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

mysql - MySQL GROUP_CONCAT 按另一列排序

问题描述

解决方案

推荐阅读