mysql - 基于字段值的行到列

Question

我有两个表，docs和 people .

• docs表包含:id和 doc_id
• people表包含:id , doc_id , type , firstname , lastname

我想要什么group_concat基于 people.type并拥有 type在它自己的专栏中。

TABLE 架构和数据:

CREATE TABLE IF NOT EXISTS `people` (
    `id` int(6) unsigned NOT NULL,
    `doc_id` varchar(6) NOT NULL,
    `type` varchar(6) NOT NULL,
    `firstname` varchar(25) NOT NULL,
    `lastname` varchar(25) NOT NULL
      ) DEFAULT CHARSET=utf8;

INSERT INTO people (`id`, `doc_id`,`type`,`firstname`,`lastname`) VALUES
('1','ABC001','From','John','Smith'),
('2','ABC001','To','Jonas','Doot'),
('3','ABC001','To','John','Howard'),
('4','ABC002','From','Holland','Jones'),
('5','ABC002','To','Tim','Jones'),
('6','ABC002','To','Julia','Smith');

CREATE TABLE IF NOT EXISTS `docs` (
    `id` int(6) unsigned NOT NULL,
    `doc_id` varchar(6) NOT NULL
      ) DEFAULT CHARSET=utf8;

INSERT INTO docs (`id`,`doc_id`) VALUES 
('1', 'ABC001'),
('2', 'ABC002');

这是我目前拥有的，它可以工作，但对于相同的 doc_id 仍然存在“重复”行，我需要的是“合并”具有相同 doc_id 的行合二为一。

SELECT d.doc_id, 
       p.type, 
       CASE p.type WHEN 'From' 
            THEN GROUP_CONCAT(CASE WHEN p.type = 'From' 
                 THEN CONCAT(p.firstname,' ',p.lastname) END SEPARATOR '; ') END 'From', 
       CASE p.type WHEN 'To' 
            THEN GROUP_CONCAT(CASE WHEN p.type = 'To' 
                 THEN CONCAT(p.firstname,' ',p.lastname) END SEPARATOR '; ') END 'To' 
            FROM docs d 
      INNER JOIN people p ON d.doc_id = p.doc_id 
GROUP BY d.doc_id, p.type

我当前的输出是:

我要找的是:

doc_id       From             To
ABC001       John Smith       John Howard; Jonas Doot
ABC002       Holland Jones    Tim Jones; Julia Smith

SQL fiddle : http://sqlfiddle.com/#!9/096f891/4

Answer 1

从 GROUP BY 中取出 p.type，这样您就不会将每种类型都放在自己的行中。

另外，CASE 表达式应该在 GROUP_CONCAT 中。聚合函数应位于分组查询的顶层。

SELECT 
    d.doc_id, 
    GROUP_CONCAT(
        CASE p.type WHEN 'From' 
            THEN CONCAT(p.firstname,' ',p.lastname) 
        END
        SEPARATOR '; '
   ) 'From', 
   GROUP_CONCAT(
        CASE p.type WHEN 'To' 
            THEN CONCAT(p.firstname,' ',p.lastname) 
        END 
        SEPARATOR '; ') 'To' 
FROM docs d 
INNER JOIN people p ON d.doc_id = p.doc_id 
GROUP BY d.doc_id