设为首页 收藏本站

运维网

查看: 739|回复: 0

[经验分享] mysql行转列

[复制链接]

尚未签到

发表于 2018-10-4 09:52:30 | 显示全部楼层 |阅读模式
  转自:http://blog.csdn.net/zhoushengchao/article/details/7321688
  在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。
  http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198
  现整理解法如下:
  数据样本:
  create table tx(
  id int primary key,
  c1 char(2),
  c2 char(2),
  c3 int
  );
  insert into tx values
  (1 ,'A1','B1',9),
  (2 ,'A2','B1',7),
  (3 ,'A3','B1',4),
  (4 ,'A4','B1',2),
  (5 ,'A1','B2',2),
  (6 ,'A2','B2',9),
  (7 ,'A3','B2',8),
  (8 ,'A4','B2',5),
  (9 ,'A1','B3',1),
  (10 ,'A2','B3',8),
  (11 ,'A3','B3',8),
  (12 ,'A4','B3',6),
  (13 ,'A1','B4',8),
  (14 ,'A2','B4',2),
  (15 ,'A3','B4',6),
  (16 ,'A4','B4',9),
  (17 ,'A1','B4',3),
  (18 ,'A2','B4',5),
  (19 ,'A3','B4',2),
  (20 ,'A4','B4',5);
  mysql> select * from tx;
  +----+------+------+------+

  |>  +----+------+------+------+
  |  1 | A1   | B1   |    9 |
  |  2 | A2   | B1   |    7 |
  |  3 | A3   | B1   |    4 |
  |  4 | A4   | B1   |    2 |
  |  5 | A1   | B2   |    2 |
  |  6 | A2   | B2   |    9 |
  |  7 | A3   | B2   |    8 |
  |  8 | A4   | B2   |    5 |
  |  9 | A1   | B3   |    1 |
  | 10 | A2   | B3   |    8 |
  | 11 | A3   | B3   |    8 |
  | 12 | A4   | B3   |    6 |
  | 13 | A1   | B4   |    8 |
  | 14 | A2   | B4   |    2 |
  | 15 | A3   | B4   |    6 |
  | 16 | A4   | B4   |    9 |
  | 17 | A1   | B4   |    3 |
  | 18 | A2   | B4   |    5 |
  | 19 | A3   | B4   |    2 |
  | 20 | A4   | B4   |    5 |
  +----+------+------+------+
  20 rows in set (0.00 sec)
  mysql>
  期望结果
  +------+-----+-----+-----+-----+------+
  |C1    |B1   |B2   |B3   |B4   |Total |
  +------+-----+-----+-----+-----+------+
  |A1    |9    |2    |1    |11   |23    |
  |A2    |7    |9    |8    |7    |31    |
  |A3    |4    |8    |8    |8    |28    |
  |A4    |2    |5    |6    |14   |27    |
  |Total |22   |24   |23   |40   |109   |
  +------+-----+-----+-----+-----+------+
  1. 利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total
  mysql> SELECT
  ->     IFNULL(c1,'total') AS total,
  ->     SUM(IF(c2='B1',c3,0)) AS B1,
  ->     SUM(IF(c2='B2',c3,0)) AS B2,
  ->     SUM(IF(c2='B3',c3,0)) AS B3,
  ->     SUM(IF(c2='B4',c3,0)) AS B4,
  ->     SUM(IF(c2='total',c3,0)) AS total
  -> FROM (
  ->     SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) AS c3
  ->     FROM tx
  ->     GROUP BY c1,c2
  ->     WITH ROLLUP
  ->     HAVING c1 IS NOT NULL
  -> ) AS A
  -> GROUP BY c1
  -> WITH ROLLUP;
  +-------+------+------+------+------+-------+
  | total | B1   | B2   | B3   | B4   | total |
  +-------+------+------+------+------+-------+
  | A1    |    9 |    2 |    1 |   11 |    23 |
  | A2    |    7 |    9 |    8 |    7 |    31 |
  | A3    |    4 |    8 |    8 |    8 |    28 |
  | A4    |    2 |    5 |    6 |   14 |    27 |
  | total |   22 |   24 |   23 |   40 |   109 |
  +-------+------+------+------+------+-------+
  5 rows in set, 1 warning (0.00 sec)
  2. 利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total
  mysql> select c1,
  -> sum(if(c2='B1',C3,0)) AS B1,
  -> sum(if(c2='B2',C3,0)) AS B2,
  -> sum(if(c2='B3',C3,0)) AS B3,
  -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
  -> from tx
  -> group by C1
  -> UNION
  -> SELECT 'TOTAL',sum(if(c2='B1',C3,0)) AS B1,
  -> sum(if(c2='B2',C3,0)) AS B2,
  -> sum(if(c2='B3',C3,0)) AS B3,
  -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) FROM TX
  -> ;
  +-------+------+------+------+------+-------+
  | c1    | B1   | B2   | B3   | B4   | TOTAL |
  +-------+------+------+------+------+-------+
  | A1    |    9 |    2 |    1 |   11 |    23 |
  | A2    |    7 |    9 |    8 |    7 |    31 |
  | A3    |    4 |    8 |    8 |    8 |    28 |
  | A4    |    2 |    5 |    6 |   14 |    27 |
  | TOTAL |   22 |   24 |   23 |   40 |   109 |
  +-------+------+------+------+------+-------+
  5 rows in set (0.00 sec)
  mysql>
  3.  利用SUM(IF()) 生成列,直接生成结果不再利用子查询
  mysql> select ifnull(c1,'total'),
  -> sum(if(c2='B1',C3,0)) AS B1,
  -> sum(if(c2='B2',C3,0)) AS B2,
  -> sum(if(c2='B3',C3,0)) AS B3,
  -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
  -> from tx
  -> group by C1 with rollup ;
  +--------------------+------+------+------+------+-------+
  | ifnull(c1,'total') | B1   | B2   | B3   | B4   | TOTAL |
  +--------------------+------+------+------+------+-------+
  | A1                 |    9 |    2 |    1 |   11 |    23 |
  | A2                 |    7 |    9 |    8 |    7 |    31 |
  | A3                 |    4 |    8 |    8 |    8 |    28 |
  | A4                 |    2 |    5 |    6 |   14 |    27 |
  | total              |   22 |   24 |   23 |   40 |   109 |
  +--------------------+------+------+------+------+-------+
  5 rows in set (0.00 sec)
  mysql>
  4. 动态,适用于列不确定情况,
  mysql> SET @EE='';
  mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=/'',C2,'/'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;
  mysql>  SET @QQ=CONCAT('SELECT ifnull(c1,/'total/'),',LEFT(@EE,LENGTH(@EE)-1),'  ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
  Query OK, 0 rows affected (0.00 sec)
  mysql> PREPARE stmt2 FROM @QQ;
  Query OK, 0 rows affected (0.00 sec)
  Statement prepared
  mysql> EXECUTE stmt2;
  +--------------------+------+------+------+------+-------+
  | ifnull(c1,'total') | B1   | B2   | B3   | B4   | TOTAL |
  +--------------------+------+------+------+------+-------+
  | A1                 |    9 |    2 |    1 |   11 |    23 |
  | A2                 |    7 |    9 |    8 |    7 |    31 |
  | A3                 |    4 |    8 |    8 |    8 |    28 |
  | A4                 |    2 |    5 |    6 |   14 |    27 |
  | total              |   22 |   24 |   23 |   40 |   109 |
  +--------------------+------+------+------+------+-------+
  5 rows in set (0.00 sec)
  mysql>
  以上均由网友  liangCK , wwwwb , WWWWA , dap570 提供, 再次感谢他们的支持。
  其实数据库中也可以用 CASE WHEN / DECODE 代替 IF


运维网声明 1、欢迎大家加入本站运维交流群:群①:263444886群②:197202523群③:485755530群④:201730672群⑤:202807635运维网交流群⑥:281548029
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@yunvn.com 网址:www.iyunv.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@yunvn.com,QQ:1061981298 本贴地址:https://www.iyunv.com/thread-611731-1-1.html 上篇帖子: Mysql的索引 下篇帖子: 有关mysql命令及用法
点击关注更多内容
您需要登录后才可以回帖 登录 | 立即注册  

本版积分规则  允许回帖邮件提醒楼主

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服 E-mail:kefu@yunvn.com

本站由青云提供云计算服务

运维网--中国最专业的运维工程师交流社区

京ICP备14039699号-1 Copyright © 2012-2020

使用手机软件扫描微信二维码

关注我们可获取更多热点资讯

Good good study day day up


客服E-mail:kefu@yunvn.com 在线客服QQ:点击这里给我发消息


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud bjyun

快速回复 返回顶部 返回列表