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中ORDER BY如何优化_mysql排序优化方法

mysql如何支持领域驱动设计_mysql DDD数据库建模思路

mysql环境搭建后客户端字符集不一致问题处理

mysql中的锁定冲突与性能分析

mysql什么时候会加行锁_mysql执行原理解析