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 替换成 '总计'。