mysql行列转换方法总结

php中文网

发布时间：2016-06-07 15:34:22

1270人浏览过

来源于php中文网

原创

转 http://bbs.csdn.net/topics/310045927 在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特集思广义。无论对错皆有分。数据样本： createtabletx( idintprimarykey, c1char(2), c2char(2), c3int ); inser

转 http://bbs.csdn.net/topics/310045927

在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特集思广义。无论对错皆有分。

数据样本：
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);

结果 (可不带行/列汇总)
[code=BatchFile]+------+-----+-----+-----+-----+------+
|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   |
+------+-----+-----+-----+-----+------+[/code]

MVM mall 网上购物系统

采用 php+mysql 数据库方式运行的强大网上商店系统，执行效率高速度快，支持多语言，模板和代码分离，轻松创建属于自己的个性化用户界面 v3.5更新： 1).进一步静态化了活动商品. 2).提供了一些重要UFT-8转换文件 3).修复了除了网银在线支付其它支付显示错误的问题. 4).修改了LOGO广告管理,增加LOGO链接后主页LOGO路径错误的问题 5).修改了公告无法发布的问题,可能是打压

下载

方法一：

"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)

方法二：

静态：
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

方法三：

动态：
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;

方法四：

--木有合计,在SQL Server下测试通过

if object_id('tempdb..#tx') is not null drop table #tx
go
create table #tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
);

insert into #tx (id,c1,c2,c3) values (1 ,'A1','B1',9)
insert into #tx (id,c1,c2,c3) values (2 ,'A2','B1',7)
insert into #tx (id,c1,c2,c3) values (3 ,'A3','B1',4)
insert into #tx (id,c1,c2,c3) values (4 ,'A4','B1',2)
insert into #tx (id,c1,c2,c3) values (5 ,'A1','B2',2)
insert into #tx (id,c1,c2,c3) values (6 ,'A2','B2',9)
insert into #tx (id,c1,c2,c3) values (7 ,'A3','B2',8)
insert into #tx (id,c1,c2,c3) values (8 ,'A4','B2',5)
insert into #tx (id,c1,c2,c3) values (9 ,'A1','B3',1)
insert into #tx (id,c1,c2,c3) values (10 ,'A2','B3',8)
insert into #tx (id,c1,c2,c3) values (11 ,'A3','B3',8)
insert into #tx (id,c1,c2,c3) values (12 ,'A4','B3',6)
insert into #tx (id,c1,c2,c3) values (13 ,'A1','B4',8)
insert into #tx (id,c1,c2,c3) values (14 ,'A2','B4',2)
insert into #tx (id,c1,c2,c3) values (15 ,'A3','B4',6)
insert into #tx (id,c1,c2,c3) values (16 ,'A4','B4',9)
insert into #tx (id,c1,c2,c3) values (17 ,'A1','B4',3)
insert into #tx (id,c1,c2,c3) values (18 ,'A2','B4',5)
insert into #tx (id,c1,c2,c3) values (19 ,'A3','B4',2)
insert into #tx (id,c1,c2,c3) values (20 ,'A4','B4',5)

SELECT     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]
FROM       #tx
GROUP BY   c1

方法五：

--动态的话

if object_id('tempdb..#tx') is not null drop table #tx
go
create table #tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
);

select * from #tx

declare @sql varchar(8000)
set @sql='select c1,'
select @sql=@sql+'sum(case when c2='''+cast(c2 as varchar(10))+''' then c3 else 0 end)['+cast(c2 as varchar(10))+'],'
from (select distinct c2 from #tx ) a
print(@sql)

set @sql=left(@sql,len(@sql)-1)+' from #tx group by c1'
exec(@sql)
if object_id('tempdb..#tx') is not null drop table #tx

/*
A1 9 2 1 11
A2 7 9 8 7
A3 4 8 8 8
A4 2 5 6 14
*/

测试

1、

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、

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、

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>

5、

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>

mysql如何安全升级版本_mysql版本升级完整流程

mysql主从复制如何查看同步状态_mysql监控方法说明

mysql在Docker环境中配置持久化存储的最佳实践

mysql索引碎片会影响性能吗_mysql索引维护方法

mysql索引优化在高并发场景有用吗_mysql实践效果说明