MySQL交叉表_MySQL

來(lái)源：懂視網(wǎng) 責(zé)編：小采時(shí)間：2020-11-09 20:11:24

MySQL交叉表_MySQL

MySQL交叉表_MySQL:在某些數(shù)據(jù)庫(kù)中有交叉表，但在MySQL中卻沒(méi)有這個(gè)功能，但網(wǎng)上看到有不少朋友想找出一個(gè)解決方法，特發(fā)貼集思廣義。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html96198現(xiàn)整理解法如下：數(shù)據(jù)樣本：cr

推薦度：

點(diǎn)擊下載本文 文檔為doc格式

導(dǎo)讀MySQL交叉表_MySQL:在某些數(shù)據(jù)庫(kù)中有交叉表，但在MySQL中卻沒(méi)有這個(gè)功能，但網(wǎng)上看到有不少朋友想找出一個(gè)解決方法，特發(fā)貼集思廣義。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html96198現(xiàn)整理解法如下：數(shù)據(jù)樣本：cr

在某些數(shù)據(jù)庫(kù)中有交叉表，但在MySQL中卻沒(méi)有這個(gè)功能，但網(wǎng)上看到有不少朋友想找出一個(gè)解決方法，特發(fā)貼集思廣義。
http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198
現(xiàn)整理解法如下：

數(shù)據(jù)樣本：

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;
+----+------+------+------+
| 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 |
+----+------+------+------+
20 rows in set (0.00 sec)
mysql>

期望結(jié)果

+------+-----+-----+-----+-----+------+
|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將匯總行標(biāo)題顯示為 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將匯總行標(biāo)題顯示為 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()) 生成列,直接生成結(jié)果不再利用子查詢

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. 動(dòng)態(tài)，適用于列不確定情況，

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>

其實(shí)數(shù)據(jù)庫(kù)中也可以用 CASE WHEN / DECODE 代替 IF

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com

MySQL交叉表_MySQL

推薦度：

點(diǎn)擊下載本文 文檔為doc格式

標(biāo)簽： mysql 交叉表 MySQL，交叉表

熱門(mén)焦點(diǎn)

MySQL交叉表_MySQL

MySQL交叉表_MySQL

MySQL交叉表_MySQL

最新推薦

猜你喜歡

熱門(mén)推薦