MySQL讲义第 34 讲——select 查询之动态交叉表查询

交叉表分为静态交叉表和动态交叉表。静态交叉表中的列是固定的，而且要提前确定要进行行列转换的列中的数据内容，在使用中灵活性不够。

动态交叉表中的列根据要进行行列转换的列中的数据动态生成，不用提前确定该列的数据内容，甚至不用知道该列的数据是什么。

一、实现动态交叉表的基本原理

实现动态交叉表的基本思想为：使用 GROUP_CONCAT 函数取出要进行行列转换的列中的所有数据，再使用 CONCAT 函数拼接成和静态交叉表查询相同的 SELECT 语句。然后使用预处理（prepare）命令执行，得到最终的查询结果。

二、拼接动态交叉表查询字符串

有下面三张表，表结构和数据如下：

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、根据 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)

2、使用 GROUP_CONCAT 函数取出 categories 列的所有数据

mysql> SELECT -> GROUP_CONCAT(DISTINCT categories) -> FROM -> users u JOIN orders o ON u.user_id = o.user_id -> JOIN goods g ON o.goods_id = g.goods_id; +-----------------------------------+ | GROUP_CONCAT(DISTINCT categories) | +-----------------------------------+ | 文具,服装,水果 | +-----------------------------------+ 1 row in set (0.00 sec)

3、结合 CONCAT 函数生成拼接字符串

--说明：为 \' 为转移字符，把单引号（'）拼接到字符串中 SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(categories=\'',categories,'\',o.quantity * g.price,0)) AS ',categories) ) INTO @sql_str FROM users u JOIN orders o ON u.user_id = o.user_id JOIN goods g ON o.goods_id = g.goods_id; mysql> SELECT @sql_str; +--------------------------------------------------------------------------------------------+| @sql_str |+-------------------------------------------------------------------------------------------+| 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 水果 |+--------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

4、拼接完整的 SELECT 语句

SET @sql_str = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(categories=\'',categories,'\',o.quantity * g.price,0)) AS ',categories) ) INTO @sql_str FROM users u JOIN orders o ON u.user_id = o.user_id JOIN goods g ON o.goods_id = g.goods_id; set @sql_str = CONCAT('SELECT IFNULL(u.user_name,\'总计\') AS 姓名,', @sql_str, ',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;'); SELECT @sql_str; +-----------------------------------------------------------------------------------------------+| @sql_str |+---------------------------------------------------------------------------------------------+| 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; +---------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

三、执行拼接字符串对应的 SQL 语句

使用预处理（prepare）命令执行拼接字符串对应的 SQL 语句，得到最终的查询结果。

SET @sql_str = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(categories=\'',categories,'\',o.quantity * g.price,0)) AS ',categories) ) INTO @sql_str FROM users u JOIN orders o ON u.user_id = o.user_id JOIN goods g ON o.goods_id = g.goods_id; set @sql_str = CONCAT('SELECT IFNULL(u.user_name,\'总计\') AS 姓名,', @sql_str, ',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;'); PREPARE stmt from @sql_str; EXECUTE stmt; DEALLOCATE prepare stmt; --- 查询结果如下： +-----------+----------+-----------+----------+-----------+ | 姓名 | 文具 | 服装 | 水果 | 总金额 | +-----------+----------+-----------+----------+-----------+ | 关云常 | 25.8000 | 2088.0000 | 34.3400 | 2148.1400 | | 刘蓓 | 0.0000 | 2238.0000 | 60.4300 | 2298.4300 | | 刘选德 | 25.8000 | 208.0000 | 18.2160 | 252.0160 | | 张华 | 50.0000 | 208.0000 | 46.0600 | 304.0600 | | 张毅德 | 0.0000 | 0.0000 | 18.9900 | 18.9900 | | 赵紫龙 | 25.8000 | 150.0000 | 20.5800 | 196.3800 | | 总计 | 127.4000 | 4892.0000 | 198.6160 | 5218.0160 | +-----------+----------+-----------+----------+-----------+ 7 rows in set (0.02 sec) -- PREPARE 语句语法说明： PREPARE statement_name FROM SQL字符串; EXECUTE statement_name; --执行预处理语句 DEALLOCATE | DROP PREPARE statement_name; --删除预处理语句定义

四、把以上代码定义为存储过程

把以上代码定义为一个存储过程，就可以把代码保存到数据库中，实现重复调用。代码如下：

DROP PROCEDURE IF EXISTS sp_crosstable; DELIMITER && CREATE PROCEDURE sp_crosstable() reads SQL data BEGIN SET @sql_str = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT('SUM(IF(categories=\'',categories,'\',o.quantity * g.price,0)) AS ',categories) ) INTO @sql_str FROM users u JOIN orders o ON u.user_id = o.user_id JOIN goods g ON o.goods_id = g.goods_id; set @sql_str = CONCAT('SELECT IFNULL(u.user_name,\'总计\') AS 姓名,', @sql_str, ',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;'); PREPARE stmt from @sql_str; EXECUTE stmt; DEALLOCATE prepare stmt; END && DELIMITER ;

调用上面的存储过程：

mysql> CALL sp_crosstable; +-----------+----------+-----------+----------+-----------+ | 姓名 | 文具 | 服装 | 水果 | 总金额 | +-----------+----------+-----------+----------+-----------+ | 关云常 | 25.8000 | 2088.0000 | 34.3400 | 2148.1400 | | 刘蓓 | 0.0000 | 2238.0000 | 60.4300 | 2298.4300 | | 刘选德 | 25.8000 | 208.0000 | 18.2160 | 252.0160 | | 张华 | 50.0000 | 208.0000 | 46.0600 | 304.0600 | | 张毅德 | 0.0000 | 0.0000 | 18.9900 | 18.9900 | | 赵紫龙 | 25.8000 | 150.0000 | 20.5800 | 196.3800 | | 总计 | 127.4000 | 4892.0000 | 198.6160 | 5218.0160 | +-----------+----------+-----------+----------+-----------+ 7 rows in set (0.01 sec) Query OK, 0 rows affected (0.01 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 | 文具 | | 4401 | 手机 | 2800.00 | 电子产品 | | 4402 | 笔记本电脑 | 4800.00 | 电子产品 | | 4403 | 运动手环 | 1400.00 | 电子产品 | | 4404 | 耳机 | 240.00 | 电子产品 | | 5501 | 电视机 | 4250.00 | 家用电器 | | 5502 | 电冰箱 | 3180.00 | 家用电器 | | 5503 | 洗衣机 | 2050.00 | 家用电器 | +----------+-----------------+---------+--------------+ 16 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 | | 30 | 2020101 | 4401 | 1.00 | 2020-10-22 00:00:00 | | 31 | 2020101 | 4402 | 1.00 | 2020-10-22 00:00:00 | | 32 | 2020101 | 4403 | 1.00 | 2020-10-22 00:00:00 | | 33 | 2020102 | 5501 | 1.00 | 2020-10-22 00:00:00 | | 34 | 2020102 | 5502 | 1.00 | 2020-10-22 00:00:00 | | 35 | 2020102 | 5503 | 1.00 | 2020-10-22 00:00:00 | +----------+---------+----------+----------+---------------------+ 35 rows in set (0.00 sec)

重新调用存储过程 sp_crosstable，结果如下：

mysql> CALL sp_crosstable; +-----------+--------------+----------+-----------+----------+--------------+------------+ | 姓名 | 家用电器 | 文具 | 服装 | 水果 | 电子产品 | 总金额 | +-----------+--------------+----------+-----------+----------+--------------+------------+ | 关云常 | 0.0000 | 25.8000 | 2088.0000 | 34.3400 | 0.0000 | 2148.1400 | | 刘蓓 | 0.0000 | 0.0000 | 2238.0000 | 60.4300 | 0.0000 | 2298.4300 | | 刘选德 | 0.0000 | 25.8000 | 208.0000 | 18.2160 | 0.0000 | 252.0160 | | 张华 | 0.0000 | 50.0000 | 208.0000 | 46.0600 | 9000.0000 | 9304.0600 | | 张毅德 | 9480.0000 | 0.0000 | 0.0000 | 18.9900 | 0.0000 | 9498.9900 | | 赵紫龙 | 0.0000 | 25.8000 | 150.0000 | 20.5800 | 0.0000 | 196.3800 | | 总计 | 9480.0000 | 127.4000 | 4892.0000 | 198.6160 | 9000.0000 | 23698.0160 | +-----------+--------------+----------+-----------+----------+--------------+------------+ 7 rows in set (0.01 sec) Query OK, 0 rows affected (0.01 sec)

1