mysql - 如何按名称进行 group_concat

Question

我有一个数据透视表:

      ID              Reference          Key               Value
    -------------------------------------------------------------
    01                  001             date                03/04/2009
    02                  001             shift               1st
    03                  001             station 1           Mark
    04                  001             station 2           John
    05                  001             station 2           Macy
    06                  002             date                04/04/2009
    07                  002             shift               2nd
    08                  002             station 1           John
    09                  002             Station 1           Drey
    10                  002             Station 2           Macy

我当前有一个生成下表的 View (使用group_concat，然后按日期字段分组):

    Date            shift             station 1                station 2
    -----------------------------------------------------------------------
    03/04/2009      1st                 Mark                   John, Macy
    04/04/2009      2nd                 John, Drey              Macy

我想要:

    Operator          Date                Shift                 Station
    --------------------------------------------------------------------
    Mark             03/04/2009            1st                    1
    Macy             03/04/2009            1st                    2                
    Macy             04/04/2009            2nd                    2
    Drey             04/04/2009            2nd                    1
    John             03/04/2009            1st                    2
    John             04/04/2009            2nd                    1

发送很多!

Answer 1

请注意，我实际上并不提倡将此作为解决方案。相反，您应该标准化您的设计，特别是在类次和运算符之间的 m:n 关系方面，并且还要反射(reflect)所涉及(或应该)涉及的不同数据类型......

DROP TABLE IF EXISTS eav_hell;

CREATE TABLE eav_hell
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,entity INT NOT NULL
,attribute VARCHAR(20) NOT NULL 
,value VARCHAR(20) NOT NULL
);

INSERT INTO eav_hell (entity,attribute,value) VALUES
(1,'date','03/04/2009'),
(1,'shift','1st'),
(1,'station 1','Mark'),
(1,'station 2','John'),
(1,'station 2','Macy'),
(2,'date','04/04/2009'),
(2,'shift','2nd'),
(2,'station 1','John'),
(2,'Station 1','Drey'),
(2,'Station 2','Macy');

SELECT a.operator
     , b.date
     , b.shift
     , b.station
  FROM 
     ( SELECT DISTINCT CASE WHEN attribute IN ('station 1','station 2') THEN value END operator FROM eav_hell ) a
  JOIN 
     ( SELECT entity
            , 1 station
            , MAX(CASE WHEN attribute = 'date' THEN value END) date
            , MAX(CASE WHEN attribute = 'shift' THEN value END) shift
            , GROUP_CONCAT(CASE WHEN attribute= 'station 1' THEN value END) names
         FROM eav_hell
        GROUP 
           BY entity
        UNION
       SELECT entity
            , 2 
            , MAX(CASE WHEN attribute = 'date' THEN value END) date
            , MAX(CASE WHEN attribute = 'shift' THEN value END) shift
            , GROUP_CONCAT(CASE WHEN attribute= 'station 2' THEN value END) 
         FROM eav_hell
        GROUP 
           BY entity

     ) b
    ON FIND_IN_SET(a.operator,b.names) > 0;

    +----------+------------+-------+---------+
    | operator | date       | shift | station |
    +----------+------------+-------+---------+
    | Mark     | 03/04/2009 | 1st   |       1 |
    | John     | 04/04/2009 | 2nd   |       1 |
    | John     | 03/04/2009 | 1st   |       2 |
    | Macy     | 03/04/2009 | 1st   |       2 |
    | Macy     | 04/04/2009 | 2nd   |       2 |
    | Drey     | 04/04/2009 | 2nd   |       1 |
    +----------+------------+-------+---------+