代码之家 › 专栏 › 技术社区 › varman

将多个动态行转换为多列mysql

mysql-workbench sql mysql

varman · 技术社区 · 8 年前

我正在使用MySQL。我必须将多次重复的行分隔成列。该表具有以下结构。

但我需要这样。

注:每项记录将始终有6条记录 约会时间 但是标题和反馈它们是动态的。 我试着用

select DateTime,

但这并没有给我预期的价值。我写了一段代码(不考虑标题):

select 
    g.dateTime AS dateTime,
    g.feedback as feedback ,
    c.title AS title    
from gauge g
inner join category c on g.category_id=c.category_id AND c.title ='title' AND g.feedback ='feedback' 
group by g.dateTime

这不管用,我试过了 GROUP_CONCAT(.... SEPARATOR'.....' ) 这给我的不是预期的输出,只是在一列中给出输出。我的方法可能是错误的。

3 回复 | 直到 5 年前

Mihai 8 年前

使用动态轴,因为有许多不同的值

Fiddle to play with

drop table t;
create table t
(
  dateTime datetime,
  title varchar(50),
  feedback int
);

insert into t values
( '2018-06-29 12:55:36', 'A', 1),
( '2018-06-29 12:55:36', 'B', 2),
( '2018-06-22 12:55:36', 'A', 1),
( '2018-06-22 12:55:36', 'B', 2),
( '2018-06-22 12:55:36', 'C', 3);

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN title = ''',
      title,
      ''' THEN ''', title ,''' END) AS ',
      CONCAT(' Title',title,',')
    ),
     CONCAT(
      'MAX(CASE WHEN feedback = ''',
      feedback,
      ''' THEN ''', feedback ,''' END) AS ',
      CONCAT(' Feedback',feedback)
    )
  ) INTO @sql
FROM T;


SET @sql = CONCAT('SELECT dateTime, ', @sql, ' 
                   FROM t
                   GROUP BY dateTime');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Gordon Linoff 8 年前

我只需要使用条件聚合。在MySQL中枚举值有点棘手(除非您使用的是MySQL 8+:

select datetime,
       max(case when rn = 1 then title end) as title_1,
       max(case when rn = 1 then feedback end) as feedback_1,
       max(case when rn = 2 then title end) as title_2,
       max(case when rn = 2 then feedback end) as feedback_2,
       max(case when rn = 3 then title end) as title_3,
       max(case when rn = 3 then feedback end) as feedback_3,
       max(case when rn = 4 then title end) as title_4,
       max(case when rn = 4 then feedback end) as feedback_4,
       max(case when rn = 5 then title end) as title_5,
       max(case when rn = 5 then feedback end) as feedback_5,
       max(case when rn = 6 then title end) as title_6,
       max(case when rn = 6 then feedback end) as feedback_6
from (select gc.*,
             (@rn := if(@dt = g.dateTime, @rn + 1,
                        if(@dt := g.dateTime, 1, 1)
                       )
             ) as rn
      from (select g.dateTime, g.feedback, c.title AS title    
            from gauge g inner join
                 category c
                 on g.category_id = c.category_id and
                    c.title = 'title' and
                    g.feedback = 'feedback' 
            group by g.dateTime
            order by g.dateTime
           ) gc cross join
           (select @dt := '', @rn := 0) params
     ) gc
group by datetime;

我不认为过滤在起作用 c.title 和 g.feedback 这是正确的。

-1

Frederick 8 年前

这不是一种优化的方法,但您可以将它们加入表中,并根据行数模6进行过滤。这样,我们就可以将这6行分成6个不同的表

http://rextester.com/DLVV64095

SELECT MAIN.DateTime,
    TBL1.TITLE AS Title1,
    TBL1.FEEDBACK AS Feedback1,
    TBL2.TITLE AS Title2,
    TBL2.FEEDBACK AS Feedback2,
    TBL3.TITLE AS Title3,
    TBL3.FEEDBACK AS Feedback3,
    TBL4.TITLE AS Title4,
    TBL4.FEEDBACK AS Feedback4,
    TBL5.TITLE AS Title5,
    TBL5.FEEDBACK AS Feedback5,
    TBL6.TITLE AS Title6,
    TBL6.FEEDBACK AS Feedback6
FROM (
    SELECT [DATETIME] AS [DateTime]
    FROM GAUGE
    GROUP BY [DATETIME]
) MAIN
INNER JOIN (
    SELECT G.DATETIME,
        G.FEEDBACK,
        C.TITLE,
        ROW_NUMBER() OVER(PARTITION BY ORDER BY (SELECT 1) ASC) AS ROWNO
    FROM GAUGE G
    INNER JOIN CATEGORY C
        ON G.CATEGORY_ID = C.CATEGORY_ID
) TBL1
    ON TBL1.DATETIME = MAIN.[DateTime]
    AND TBL1.ROWNO % 6 = 1
INNER JOIN (
    SELECT G.DATETIME,
        G.FEEDBACK,
        C.TITLE,
        ROW_NUMBER() OVER(PARTITION BY ORDER BY (SELECT 1) ASC) AS ROWNO
    FROM GAUGE G
    INNER JOIN CATEGORY C
        ON G.CATEGORY_ID = C.CATEGORY_ID
) TBL2
    ON TBL2.DATETIME = MAIN.[DateTime]
    AND TBL2.ROWNO % 6 = 2
INNER JOIN (
    SELECT G.DATETIME,
        G.FEEDBACK,
        C.TITLE,
        ROW_NUMBER() OVER(PARTITION BY ORDER BY (SELECT 1) ASC) AS ROWNO
    FROM GAUGE G
    INNER JOIN CATEGORY C
        ON G.CATEGORY_ID = C.CATEGORY_ID
) TBL3
    ON TBL3.DATETIME = MAIN.[DateTime]
    AND TBL3.ROWNO % 6 = 3
INNER JOIN (
    SELECT G.DATETIME,
        G.FEEDBACK,
        C.TITLE,
        ROW_NUMBER() OVER(PARTITION BY ORDER BY (SELECT 1) ASC) AS ROWNO
    FROM GAUGE G
    INNER JOIN CATEGORY C
        ON G.CATEGORY_ID = C.CATEGORY_ID
) TBL4
    ON TBL4.DATETIME = MAIN.[DateTime]
    AND TBL4.ROWNO % 6 = 4
INNER JOIN (
    SELECT G.DATETIME,
        G.FEEDBACK,
        C.TITLE,
        ROW_NUMBER() OVER(PARTITION BY ORDER BY (SELECT 1) ASC) AS ROWNO
    FROM GAUGE G
    INNER JOIN CATEGORY C
        ON G.CATEGORY_ID = C.CATEGORY_ID
) TBL5
    ON TBL5.DATETIME = MAIN.[DateTime]
    AND TBL5.ROWNO % 6 = 5
INNER JOIN (
    SELECT G.DATETIME,
        G.FEEDBACK,
        C.TITLE,
        ROW_NUMBER() OVER(PARTITION BY ORDER BY (SELECT 1) ASC) AS ROWNO
    FROM GAUGE G
    INNER JOIN CATEGORY C
        ON G.CATEGORY_ID = C.CATEGORY_ID
) TBL6
    ON TBL6.DATETIME = MAIN.[DateTime]
    AND TBL6.ROWNO % 6 = 0