mysql数据透视表日期(垂直到水平数据) [英] mysql pivot table date (vertical to horizontal data)

问题描述

我一直在寻找没有答案的小时.

我要转换此表:

I have been searching for hours without a decent answer.

I want to transform this table:

Client_id    Date
-----------  ------------  
1            2013-02-03    
1            2013-02-10
1            2013-05-12
2            2013-02-03
2            2013-07-15

收件人:

Client_id    Date1          Date2         Date3         Date4, Date5, Date6...
-----------  ------------   ------------  ------------  ------------
1            2013-02-03     2013-02-10    2013-05-12
2            2013-02-03     2013-07-15

推荐答案

为了获得此结果，您需要透视数据. MySQL没有枢轴函数，但是您可以将聚集函数与CASE表达式一起使用.

In order to get this result, you will want to pivot the data. MySQL does not have a pivot function but you can use an aggregate function with a CASE expression.

如果知道日期数，则可以对查询进行硬编码:

If the number of dates is known, then you can hard-code the query:

select client_id,
  max(case when rownum = 1 then date end) Date1,
  max(case when rownum = 2 then date end) Date2,
  max(case when rownum = 3 then date end) Date3
from
(
  select client_id,
    date,
    @row:=if(@prev=client_id, @row,0) + 1 as rownum,
    @prev:=client_id 
  from yourtable, (SELECT @row:=0, @prev:=null) r
  order by client_id, date
) s
group by client_id
order by client_id, date

请参见带有演示的SQL小提琴

我实现了用户变量，以便为client_id组中的每个记录分配行号.

I implemented user variables to assign a row number to each record within the client_id group.

如果日期数量未知，则需要使用一条准备好的语句来动态创建sql:

If you have an unknown number of dates, then you will need to use a prepared statement to create the sql dynamically:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN rownum = ',
      rownum,
      ' THEN date END) AS Date_',
      rownum
    )
  ) INTO @sql
from
(
  select client_id,
    date,
    @row:=if(@prev=client_id, @row,0) + 1 as rownum,
    @prev:=client_id 
  from yourtable, (SELECT @row:=0) r
  order by client_id, date
) s
order by client_id, date;


SET @sql 
  = CONCAT('SELECT client_id, ', @sql, ' 
           from
           (
             select client_id,
               date,
               @row:=if(@prev=client_id, @row,0) + 1 as rownum,
               @prev:=client_id 
             from yourtable, (SELECT @row:=0) r
             order by client_id, date
           ) s
           group by client_id
           order by client_id, date');

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

请参见带演示的SQL小提琴.

它们都给出结果:

| CLIENT_ID |                          DATE_1 |                          DATE_2 |                     DATE_3 |
--------------------------------------------------------------------------------------------------------------
|         1 | February, 03 2013 00:00:00+0000 | February, 10 2013 00:00:00+0000 | May, 12 2013 00:00:00+0000 |
|         2 | February, 03 2013 00:00:00+0000 |     July, 15 2013 00:00:00+0000 |                     (null) |

这篇关于mysql数据透视表日期(垂直到水平数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！