MySQL条件搜索查询

Question

我想找一个作家的所有合著者。第一列是出版物的id。接下来的三列是作者的第一、中间和姓氏。

4 KARL K KWON
4 JACK A SMITH  
4 DINESH  SMITH  
5 KARL K KWON
5 JACK B SMITH
6 AMEY SCHENCK BAILEY
6 KARL K KWON
6 DINESH  SMITH
6 JACK  SMITH
13 JACK  SMITH
13 RONALD  VALE

我想得到以下输出

JACK A SMITH {DINESH SMITH, KARL K KWON}
JACK B SMITH {KARL K KWON}
JACK  SMITH {AMEY SCHENCK, KARL K KWON, DINESH SMITH, RONALD  VALE}

我无法理解要使用哪种结构来存储这些新数据，以及如何获得它。任何建议/想法..。

编辑

这不是一个简单的发布id组。例如，JACK SMITH在这里有2个ids为6和13的出版物，因此需要找到这两个出版物的共同作者，并将其添加到JACK SMITH的合著者集合中。

Answer 1

这不是完全相同的格式，但它会吗？

编辑尝试#2：

添加以下索引：

INDEX(pub_id, name)
INDEX(name, pub_id)

这是一个查询，我认为这个自连接应该可以做到：

SELECT  a.name AS Author,
        GROUP_CONCAT(DISTINCT c.name SEPARATOR ', ') AS Co_authors
    FROM  ( SELECT  DISTINCT name FROM  tbl ) AS a  -- Get the authors
    JOIN  tbl b ON (b.name = a.name)                -- Get what they published
    JOIN  tbl c ON (c.pub_id = b.pub_id)            -- Get the co-authors
    GROUP BY  a.name;

Answer 2

这将返回ids和逗号分隔的作者列表。

SELECT id, GROUP_CONCAT(CONCAT_WS(' ', firstName, middleName, lastName)) FROM table GROUP BY id

用上面的一张加入桌子

SELECT t.*, tGrouped.authors  FROM table t
JOIN (SELECT id, GROUP_CONCAT(CONCAT_WS(' ', firstName, middleName, lastName)) as authors FROM table GROUP BY id) tGrouped ON t.id=tGrouped.id

最后，如果不希望作者列表中出现名称，请使用简单的REPLACE()。