Mysql实例mysql 行列动态转换的实现(列联表,交叉表)

《Mysql实例mysql 行列动态转换的实现(列联表,交叉表)》要点：
本文介绍了Mysql实例mysql 行列动态转换的实现(列联表,交叉表)，希望对您有用。如果有疑问，可以联系我们。

MYSQL入门(1)动态,适用于列不确定情况

MYSQL入门
create table table_name(
 id int primary key,col1 char(2),col2 char(2),col3 int
);

MYSQL入门
insert into table_name values
(1,'A1','B1',9),(2,'A2',7),(3,'A3',4),(4,'A4',2),(5,'B2',(6,(7,8),(8,5),(9,'B3',1),(10,(11,(12,6),(13,'B4',(14,(15,(16,(17,3),(18,(19,(20,5);

MYSQL入门
select * from table_name;
+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
| 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入门
SET @EE='';
SELECT @EE:=CONCAT(@EE,'SUM(IF(col2='',col2,''',',col3,0)) AS ',') FROM (SELECT DISTINCT col2 FROM table_name) A;
SET @QQ=CONCAT('SELECT ifnull(col1,'total') AS columnA,LEFT(@EE,LENGTH(@EE)-1),SUM(col3) AS TOTAL FROM table_name GROUP BY col1 WITH ROLLUP');
PREPARE stmt2 FROM @QQ;
EXECUTE stmt2;

MYSQL入门
+---------+------+------+------+------+-------+
| columnA | 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 |
+---------+------+------+------+------+-------+

MYSQL入门(2)第二个字段确定的情况下使用

MYSQL入门
SELECT
  IFNULL(col1,'total') AS total,SUM(IF(col2='B1',0)) AS B1,SUM(IF(col2='B2',0)) AS B2,SUM(IF(col2='B3',0)) AS B3,SUM(IF(col2='B4',0)) AS B4,SUM(IF(col2='total',0)) AS total
 FROM (
  SELECT col1,IFNULL(col2,'total') AS col2,SUM(col3) AS col3
  FROM table_name
  GROUP BY col1,col2
  WITH ROLLUP
  HAVING col1 IS NOT NULL
 ) AS A
 GROUP BY col1
 WITH ROLLUP;

MYSQL入门注： WITH ROLLUP 用于列上求和； SUM(IF(col2='total',0)) AS total 用于行上求和.

MYSQL入门(3)第二个字段确定的情况下使用

MYSQL入门
select ifnull(col1,'total') AS col1,sum(if(col2='B1',sum(if(col2='B2',sum(if(col2='B3',sum(if(col2='B4',SUM(col3) AS TOTAL
 from table_name
 group by col1 with rollup ;

MYSQL入门以上这篇mysql 行列动态转换的实现(列联表,交叉表)就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持编程之家PHP.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!