MySQL讲义第 33 讲——select 查询之静态交叉表查询
文章目录
- MySQL讲义第 33 讲——select 查询之静态交叉表查询
- 一、数据准备
- 二、交叉表的形式
- 三、静态交叉表的实现
- 1、根据 user、goods 和 orders 三张表查询每个用户每种类别的商品的消费金额
交叉表查询是将来源于某个表中的字段进行分组，一组列在交叉表左侧，一组列在交叉表上部，并在交叉表行与列交叉处显示表中某个字段的各种计算值。交叉表分为静态交叉表和动态交叉表，其中静态交叉表中的列是固定的，而动态交叉表中的列需要动态生成。
使用交叉表查询来计算和重构数据，可以简化数据分析。交叉表查询计算数据的总和，平均值，计数及其他类型的统计，并将它们分组，一组列在数据表左侧作为交叉表的行字段，另一组列在数据表的顶端作为交叉表的列字段。
一、数据准备
有下面三张表，表结构和数据如下：
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 替换成 '总计'。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
