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

最新推荐文章于 2024-04-20 16:42:16 发布

睿思达DBA_WGX

最新推荐文章于 2024-04-20 16:42:16 发布

阅读量1k

点赞数 1

分类专栏： MySQL基本操作 MySQL讲义文章标签：数据库 mysql select 查询

本文链接：https://blog.csdn.net/weixin_44377973/article/details/109227121

版权

MySQL基本操作同时被 2 个专栏收录

169 篇文章 95 订阅

订阅专栏

MySQL讲义

57 篇文章 26 订阅

订阅专栏

本文详细介绍了如何在MySQL中实现动态交叉表查询，通过GROUP_CONCAT和CONCAT函数动态生成SQL语句，结合预处理（prepare）命令执行SQL，达到灵活处理不同数据内容的交叉表需求。并展示了将此过程封装为存储过程的方法，以便于重复调用。

摘要由CSDN通过智能技术生成

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

文章目录

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)