MySQL講義第33講——select 查詢之靜態交叉表查詢
交叉表查詢是將來源于某個表中的欄位進行分組，一組列在交叉表左側，一組列在交叉表上部，并在交叉表行與列交叉處顯示表中某個欄位的各種計算值，交叉表分為靜態交叉表和動態交叉表，其中靜態交叉表中的列是固定的，而動態交叉表中的列需要動態生成，
使用交叉表查詢來計算和重構資料，可以簡化資料分析，交叉表查詢計算資料的總和，平均值，計數及其他型別的統計，并將它們分組，一組列在資料表左側作為交叉表的行欄位，另一組列在資料表的頂端作為交叉表的列欄位，
一、資料準備
有下面三張表，表結構和資料如下：
mysql> select * from users;
+---------+-----------+-------------+-----------+
| user_id | user_name | phone | addr |
+---------+-----------+-------------+-----------+
| 2020101 | 張華 | 15937310588 | Xinxiang |
| 2020102 | 張毅德 | 13783730566 | Xinxiang |
| 2020103 | 劉選德 | 13602313277 | Beijing |
| 2020104 | 趙紫龍 | 13703713731 | Zhengzhou |
| 2020105 | 關云常 | 13637312446 | Xinxiang |
| 2020106 | 劉蓓 | 15037130526 | Zhengzhou |
| 2020107 | 劉曉璐 | 18237150566 | Zhengzhou |
+---------+-----------+-------------+-----------+
7 rows in set (0.00 sec)
mysql> select * from goods;
+----------+------------+---------+------------+
| goods_id | goods_name | price | categories |
+----------+------------+---------+------------+
| 1101 | 帽子 | 58.00 | 服裝 |
| 1102 | 褲子 | 150.00 | 服裝 |
| 1103 | 運動鞋 | 580.00 | 服裝 |
| 1104 | 西服 | 1508.00 | 服裝 |
| 2201 | 香蕉 | 2.58 | 水果 |
| 2202 | 蘋果 | 4.98 | 水果 |
| 2203 | 葡萄 | 6.88 | 水果 |
| 3301 | 筆記本 | 25.00 | 文具 |
| 3302 | 鋼筆 | 25.80 | 文具 |
+----------+------------+---------+------------+
9 rows in set (0.00 sec)
mysql> select * from orders;
+----------+---------+----------+----------+---------------------+
| order_id | user_id | goods_id | quantity | order_date |
+----------+---------+----------+----------+---------------------+
| 1 | 2020101 | 1101 | 1.00 | 2020-10-22 09:05:21 |
| 2 | 2020101 | 1102 | 1.00 | 2020-10-20 00:00:00 |
| 3 | 2020101 | 2201 | 2.50 | 2020-10-21 00:00:00 |
| 4 | 2020101 | 2202 | 4.50 | 2020-10-21 00:00:00 |
| 5 | 2020101 | 2203 | 2.50 | 2020-10-20 00:00:00 |
| 6 | 2020101 | 3301 | 2.00 | 2020-10-20 00:00:00 |
| 7 | 2020102 | 2201 | 3.50 | 2020-10-22 00:00:00 |
| 8 | 2020102 | 2202 | 2.00 | 2020-10-22 00:00:00 |
| 9 | 2020103 | 2202 | 2.00 | 2020-10-19 00:00:00 |
| 10 | 2020103 | 2203 | 1.20 | 2020-10-19 00:00:00 |
| 11 | 2020103 | 3302 | 1.00 | 2020-10-20 00:00:00 |
| 12 | 2020103 | 1101 | 1.00 | 2020-10-19 00:00:00 |
| 13 | 2020103 | 1102 | 1.00 | 2020-10-20 00:00:00 |
| 14 | 2020104 | 1102 | 1.00 | 2020-10-20 00:00:00 |
| 15 | 2020104 | 2201 | 1.80 | 2020-10-19 00:00:00 |
| 16 | 2020104 | 2202 | 3.20 | 2020-10-19 00:00:00 |
| 17 | 2020104 | 3302 | 1.00 | 2020-10-20 00:00:00 |
| 18 | 2020105 | 1103 | 1.00 | 2020-10-21 00:00:00 |
| 19 | 2020105 | 1104 | 1.00 | 2020-10-21 00:00:00 |
| 20 | 2020105 | 2201 | 1.80 | 2020-10-22 00:00:00 |
| 21 | 2020105 | 2202 | 3.20 | 2020-10-22 00:00:00 |
| 22 | 2020105 | 2203 | 2.00 | 2020-10-21 00:00:00 |
| 23 | 2020105 | 3302 | 1.00 | 2020-10-21 00:00:00 |
| 24 | 2020106 | 1102 | 1.00 | 2020-10-22 00:00:00 |
| 25 | 2020106 | 1103 | 1.00 | 2020-10-22 00:00:00 |
| 26 | 2020106 | 1104 | 1.00 | 2020-10-22 00:00:00 |
| 27 | 2020106 | 2201 | 2.00 | 2020-10-21 00:00:00 |
| 28 | 2020106 | 2202 | 3.50 | 2020-10-21 00:00:00 |
| 29 | 2020106 | 2203 | 5.50 | 2020-10-21 00:00:00 |
+----------+---------+----------+----------+---------------------+
29 rows in set (0.00 sec)
二、交叉表的形式
交叉表又稱為行列轉換，就是把下面的表1 轉換為表2（交叉表），
select stu_name,c_name,score
from stu s,course c,score sc where s.stu_id=sc.stu_id and c.c_id=sc.c_id;
--################################ 表1 #############################################
+-----------+-----------------------+-------+
| stu_name | c_name | score |
+-----------+-----------------------+-------+
| 王占峰 | 管理學原理 | 88 |
| 王占峰 | 政治經濟學 | 75 |
| 王占峰 | 資料庫系統原理 | 89 |
| 王占峰 | 企業管理概論 | 95 |
| 劉國強 | 管理學原理 | 65 |
| 劉國強 | 政治經濟學 | 79 |
| 劉國強 | 資料庫系統原理 | 72 |
| 劉國強 | 企業管理概論 | 91 |
| 王艷艷 | 管理學原理 | 84 |
| 王艷艷 | 政治經濟學 | 76 |
| 王艷艷 | 資料庫系統原理 | 68 |
| 王艷艷 | 企業管理概論 | 92 |
| 趙牡丹 | 管理學原理 | 64 |
| 趙牡丹 | 政治經濟學 | 75 |
| 趙牡丹 | 資料庫系統原理 | 81 |
| 趙牡丹 | 企業管理概論 | 90 |
+-----------+----------------------+-------+
--################################ 表2 #############################################
+-----------+--------------+---------------+-------------------+------------------+--------+
| stu_name | 管理學原理 | 政治經濟學 | 資料庫系統原理 | 企業管理概論 | 總分 |
+-----------+---------------+---------------+------------------+------------------+--------+
| 劉國強 | 65 | 79 | 72 | 91 | 307 |
| 王占峰 | 88 | 75 | 89 | 95 | 347 |
| 王艷艷 | 84 | 76 | 68 | 92 | 320 |
| 趙牡丹 | 64 | 75 | 81 | 90 | 310 |
+-----------+---------------+---------------+-----------------------+--------------+--------+
三、靜態交叉表的實作
生成交叉表的基本方法為：
（1）按照交叉表的行欄位進行分組（比如上例中的 stu_name），
（2）對要轉換為列的行欄位（比如上例中的 c_name）進行統計，在統計函式中使用 IF 函式對資料進行篩選，生成列欄位，
例如：
1、根據 user、goods 和 orders 三張表查詢每個用戶每種類別的商品的消費金額
第一步：根據 u.user_name 和 categories 兩個欄位分組，統計消費金額
mysql> SELECT
-> u.user_name as 用戶姓名,
-> categories as 消費類別,
-> sum(o.quantity * g.price) as 總金額
-> FROM
-> users u JOIN orders o ON u.user_id = o.user_id
-> JOIN goods g ON o.goods_id = g.goods_id
-> GROUP BY u.user_name,categories;
+--------------+--------------+-----------+
| 用戶姓名 | 消費類別 | 總金額 |
+--------------+--------------+-----------+
| 關云常 | 文具 | 25.8000 |
| 關云常 | 服裝 | 2088.0000 |
| 關云常 | 水果 | 34.3400 |
| 劉蓓 | 服裝 | 2238.0000 |
| 劉蓓 | 水果 | 60.4300 |
| 劉選德 | 文具 | 25.8000 |
| 劉選德 | 服裝 | 208.0000 |
| 劉選德 | 水果 | 18.2160 |
| 張華 | 文具 | 50.0000 |
| 張華 | 服裝 | 208.0000 |
| 張華 | 水果 | 46.0600 |
| 張毅德 | 水果 | 18.9900 |
| 趙紫龍 | 文具 | 25.8000 |
| 趙紫龍 | 服裝 | 150.0000 |
| 趙紫龍 | 水果 | 20.5800 |
+--------------+--------------+-----------+
15 rows in set (0.00 sec)
第二步：對消費類別欄位分別按文具、服裝、水果為條件進行篩選，然后轉換為列，代碼如下：
mysql> SELECT
-> u.user_name as 用戶,
-> sum(if(categories = '服裝',o.quantity * g.price,0)) as 服裝,
-> sum(if(categories = '文具',o.quantity * g.price,0)) as 文具,
-> sum(if(categories = '水果',o.quantity * g.price,0)) as 水果,
-> sum(o.quantity * g.price) as 總金額
-> FROM
-> users u JOIN orders o ON u.user_id = o.user_id
-> JOIN goods g ON o.goods_id = g.goods_id
-> GROUP BY u.user_name;
+-----------+-----------+---------+---------+-----------+
| 用戶 | 服裝 | 文具 | 水果 | 總金額 |
+-----------+-----------+---------+---------+-----------+
| 關云常 | 2088.0000 | 25.8000 | 34.3400 | 2148.1400 |
| 劉蓓 | 2238.0000 | 0.0000 | 60.4300 | 2298.4300 |
| 劉選德 | 208.0000 | 25.8000 | 18.2160 | 252.0160 |
| 張華 | 208.0000 | 50.0000 | 46.0600 | 304.0600 |
| 張毅德 | 0.0000 | 0.0000 | 18.9900 | 18.9900 |
| 趙紫龍 | 150.0000 | 25.8000 | 20.5800 | 196.3800 |
+-----------+-----------+---------+---------+-----------+
6 rows in set (0.00 sec)
第三步：添加總計行
mysql> SELECT
-> IFNULL(u.user_name,'總計') as 用戶,
-> sum(if(categories = '服裝',o.quantity * g.price,0)) as 服裝,
-> sum(if(categories = '文具',o.quantity * g.price,0)) as 文具,
-> sum(if(categories = '水果',o.quantity * g.price,0)) as 水果,
-> sum(o.quantity * g.price) as 總金額
-> FROM
-> users u JOIN orders o ON u.user_id = o.user_id
-> JOIN goods g ON o.goods_id = g.goods_id
-> GROUP BY u.user_name
-> WITH rollup;
+-----------+-----------+----------+----------+-----------+
| 用戶 | 服裝 | 文具 | 水果 | 總金額 |
+-----------+-----------+----------+----------+-----------+
| 關云常 | 2088.0000 | 25.8000 | 34.3400 | 2148.1400 |
| 劉蓓 | 2238.0000 | 0.0000 | 60.4300 | 2298.4300 |
| 劉選德 | 208.0000 | 25.8000 | 18.2160 | 252.0160 |
| 張華 | 208.0000 | 50.0000 | 46.0600 | 304.0600 |
| 張毅德 | 0.0000 | 0.0000 | 18.9900 | 18.9900 |
| 趙紫龍 | 150.0000 | 25.8000 | 20.5800 | 196.3800 |
| 總計 | 4892.0000 | 127.4000 | 198.6160 | 5218.0160 |
+-----------+-----------+----------+----------+-----------+
7 rows in set (0.00 sec)
--說明：使用 IFNULL 函式把最后一行第一列的 NULL 替換成 '總計'，
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/187714.html
標籤：其他
上一篇：吊打MySQL：21性能優化實踐+學習導圖+55面試+筆記+20高頻知識點
下一篇：MySQL鎖的基本介紹