Mysql 行转列

最新推荐文章于 2024-09-09 15:14:50 发布

weixin_30919919

最新推荐文章于 2024-09-09 15:14:50 发布

阅读量125

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/shengwei/p/5706919.html

版权

select username,count(1) from T_CheckData group by username;

/*=====确定列 第一种=====*/
select sum(case username when '郭二' then 1 else 0 END) as 郭二,
sum(case username when '张三' then 1 else 0 END) as 张三,
sum(case username when '李四' then 1 else 0 END) as 李四,
sum(case username when '王五' then 1 else 0 END) as 王五
 from T_CheckData ;

/*=====确定列 第二种=====*/
select sum(if(username='郭二',1,0)) as 郭二,
sum(if(username='张三',1,0)) as 张三,
sum(if(username='李四',1,0)) as 李四,
sum(if(username='王五',1,0)) as 王五
 from T_CheckData ;


/*=====不确定列=====*/
/*=====【username】替换为你想要的动态列值=====*/
/*=====【state】替换为用于合计的数值=====*/
/*=====【weight】替换为你的分类值（一般为日期）=====*/
/*=====【T_CheckData】替换为表名=====*/
SET @EE='';
set @str_tmp='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(username=\'',username,'\'',',state,0)) AS ',username,',') as aa into @str_tmp FROM (SELECT DISTINCT username FROM T_CheckData) A order by length(aa) desc limit 1; 
SET @QQ=CONCAT('SELECT ifnull(T_CheckData.weight,\'total\') as 分类,',LEFT(@str_tmp,char_length(@str_tmp)-1),'  ,SUM(state) AS TOTAL FROM T_CheckData GROUP BY weight WITH ROLLUP');
PREPARE stmt  FROM @QQ; 
EXECUTE stmt ;
deallocate prepare stmt;