表结果: create table user(id int ,username varchar(10),create_time datetime,type int )insert into user (`id`,`username`,`create_time`,`type`)values ('1','张三','2019-04-25 14:36:41','1'),('1','张三','2019-04-25 14:37:10','2'),('1','张三','2019-04-25 14:37:26','1'),('1','张三','2019-04-27 14:38:08','1'),('1','张三','2019-04-26 14:36:02','1'),('1','张三','2019-04-26 14:36:19','2'),('1','李四','2019-04-25 14:39:50','1'),('1','李四','2019-04-26 14:39:49','1'),('1','李四','2019-04-26 14:39:48','1'),('1','李四','2019-04-27 14:39:48','1'),('1','李四','2019-04-27 14:39:44','1'),('1','王五','2019-04-25 14:36:41','1');
数据表:
静态用 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
posted on 2019-04-27 22:20 阅读( ...) 评论( ...)