mysql 动态行转列

程序员俊杰技术 2022年11月19日

0 收藏 340 点赞 4,630 浏览 1050 个字

表结果：
create table user(
id int ,
username varchar(10),
create_time datetime,
type int
)insert into user (`id`,`username`,`create_time`,`type`)
values ('','张三','2019-04-25 14:36:41',''),
('','张三','2019-04-25 14:37:10',''),
('','张三','2019-04-25 14:37:26',''),
('','张三','2019-04-27 14:38:08',''),
('','张三','2019-04-26 14:36:02',''),
('','张三','2019-04-26 14:36:19',''),
('','李四','2019-04-25 14:39:50',''),
('','李四','2019-04-26 14:39:49',''),
('','李四','2019-04-26 14:39:48',''),
('','李四','2019-04-27 14:39:48',''),
('','李四','2019-04-27 14:39:44',''),
('','王五','2019-04-25 14:36:41','');

数据表：

静态用 case when then

mysql 只能，动态用存储，

SQL Server中行列转换用 Pivot UnPivot

结果：

sql:

SET @str='';
set @sql_temp='';
SELECT @str:=CONCAT(@str,'sum(IF(left(create_time,10)=\'',create_time,'\'',',1,0)',') AS ' ,'''',create_time,'''',',') as aa into @sql_temp FROM (
SELECT left(create_time,10) as create_time  FROM `user` group by left(create_time,10)) A order by length(aa) desc limit 1;
SET @result_sql=CONCAT('SELECT  user.username, ',LEFT(@sql_temp,char_length(@sql_temp)-1),'  FROM user GROUP BY username' );
PREPARE stmt FROM @result_sql;
EXECUTE stmt ;
deallocate prepare stmt