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/ruanti/187125.html

標籤：其他

上一篇：吊打MySQL：21性能優化實踐+學習導圖+55面試+筆記+20高頻知識點

下一篇：MySQL鎖的基本介紹