mysql 按时间 垂直_mysql数据透视表日期(垂直于水平数据)

为了获得此结果,您需要透视数据. MySQL没有pivot函数,但你可以使用带有CASE表达式的聚合函数.

如果已知日期数,则可以对查询进行硬编码：

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

我实现了用户变量来为client_id组中的每条记录分配一个行号.

如果您的日期数量未知,则需要使用预准备语句动态创建sql：

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;

他们都给出了结果：

| 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) |