设为首页 收藏本站

运维网

查看: 644|回复: 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、联系人Email:admin@yunvn.com 网址:www.iyunv.com

点击关注更多内容
您需要登录后才可以回帖 登录 | 立即注册  

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

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

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

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

扫描微信二维码查看详情

客服 E-mail:kefu@yunvn.com

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

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

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

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

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

Good good study day day up !


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


独家合作伙伴: 青云cloud

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