RJ博客

MySQL行列转置方法

本文目录

MySQL行列转置数据样本:

-- 表的结构 `tx` --
DROP TABLE IF EXISTS `tx`;
CREATE TABLE IF NOT EXISTS `tx` (
  `id` int(11) NOT NULL,
  `c1` char(2) DEFAULT NULL,
  `c2` char(2) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- 转存表中的数据 `tx`--
INSERT INTO `tx` (`id`, `c1`, `c2`, `c3`) 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);

数据表数据:

+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  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 |
+----+------+------+------+

期望结果:

+------+-----+-----+-----+-----+------+
|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

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;

2.利用SUM(IF()) 生成列 + UNION 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total

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
    ;

3.利用SUM(IF()) 生成列,直接生成结果不再利用子查询

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;

########## 另一种写法 ##########

SELECT ifnull(c1,'TOTAL') AS C1
    ,sum(CASE
            WHEN c2='B1' THEN c3
            ELSE 0
        END) AS B1
    ,sum(CASE
            WHEN c2='B2' THEN c3
            ELSE 0
        END) AS B2
    ,sum(CASE
            WHEN c2='B3' THEN c3
            ELSE 0
        END) AS B3
    ,sum(CASE
            WHEN c2='B4' THEN c3
            ELSE 0
        END) AS B4
    ,sum(c3) AS TOTAL
FROM tx
GROUP BY c1 with rollup ;

4.动态,适用于列不确定情况

SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A;
SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;


相关函数解释:

with rollup用法?

with rollup可以得到每个分组的汇总级别的数据。

IF(expr1,expr2,expr3)的用法? (可以用CASE when来实现)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),则 IF()的返回值为 expr2; 否则返回值则为 expr3。IF()的返回值为数字值或字符串值,具体情况视其所在语境而定。

IFNULL(expr1,expr2)的用法?

假如 expr1不为 NULL,则 IFNULL()的返回值为 expr1; 否则其返回值为 expr2。

:=符号是什么?

例如@num:=@num+1,:=是赋值的作用,所以,先执行@num+1,然后再赋值给@num。 

如何打印变量?

select 变量;

CONCAT()函数?

将多个字符串连接成一个字符串,示例:

mysql CONCAT(str1,str2,…) 

LEFT()函数?

返回一定长度的子字符串。这两个函数的区别是,它们返回的分别是字符串的不同部分。LEFT()函数返回字符串最左边的字符,顺序从左数到右。

prepare、execute函数?

预处理语句,使用PAREPARE STATEMENT可以减少每次执行SQL的语法分析,比如用于执行带有WHERE条件的SELECT和DELETE,或者UPDATE,或者INSERT,只需要每次修改变量值即可。同样可以防止SQL注入,参数值可以包含转义符和定界符。




References:

http://blog.csdn.net/truelove12358/article/details/49453679

http://blog.chinaunix.net/uid-7692530-id-2567543.html

http://blog.chinaunix.net/uid-411974-id-3990697.html


http://jbm3072.iteye.com/blog/1168429

http://outofmemory.cn/code-snippet/1149/MySQL-if-case-statement-usage-summary

http://blog.csdn.net/wangjun5159/article/details/51378558

http://database.51cto.com/art/201010/229188.htm

http://book.51cto.com/art/200908/145904.htm

http://blog.itpub.net/29773961/viewspace-1852824

相关推荐

发表评论