数据库系统原理与应用教程（052）—— MySQL 的数据完整性（十四）：交叉表查询（行列转换）

数据库系统原理与应用教程（052）—— MySQL 的数据完整性（十四）：交叉表查询（行列转换）
- 一、交叉表的形式
- 二、静态交叉表
- 三、动态交叉表
- - - 1、取出要进行行列转换的列中的数据
    - 2、使用 CONCAT 函数生成拼接字符串
    - 3、拼接完整的 SELECT 语句
    - 4、使用预处理命令执行拼接好的字符串
    - 5、把交叉表查询定义为存储过程

交叉表查询是将表中的列进行分组，一组列在交叉表左侧，一组列在交叉表上部，并在交叉表行与列交叉处显示表中某个字段的各种计算值。

交叉表分为静态交叉表和动态交叉表。静态交叉表中的列是固定的，而且要提前确定要进行行列转换的列中的数据内容，在使用中灵活性不够。动态交叉表中的列根据要进行行列转换的列中的数据动态生成，不用提前确定该列的数据内容，甚至不用知道该列的数据是什么。

使用交叉表查询来计算和重构数据，可以简化数据分析。交叉表查询计算数据的和，平均值，计数及其他类型的统计，并将它们分组，一组列在数据表左侧作为交叉表的行字段，另一组列在数据表的顶端作为交叉表的列字段。

一、交叉表的形式

交叉表查询又称为行列转换，就是把下面的查询结果转换为表1（交叉表）。

/*
select s.s_id, s.s_name, c.c_id, c.c_name, sc.score
from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id;
*/mysql> select s.s_id, s.s_name, c.c_id, c.c_name, sc.score-> from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id;
+-------+-----------+------+--------------+-------+
| s_id  | s_name    | c_id | c_name       | score |
+-------+-----------+------+--------------+-------+
| S2011 | 张晓刚    | C102 | 高等数学     |    84 |
| S2011 | 张晓刚    | C105 | 传染病学     |    90 |
| S2012 | 刘小青    | C101 | 古代文学     |    67 |
| S2012 | 刘小青    | C102 | 高等数学     |    52 |
| S2012 | 刘小青    | C103 | 线性代数     |    55 |
| S2012 | 刘小青    | C104 | 临床医学     |    86 |
| S2012 | 刘小青    | C105 | 传染病学     |    87 |
| S2013 | 曹梦德    | C102 | 高等数学     |    97 |
| S2013 | 曹梦德    | C103 | 线性代数     |    68 |
| S2013 | 曹梦德    | C104 | 临床医学     |    66 |
| S2013 | 曹梦德    | C105 | 传染病学     |    68 |
| S2014 | 刘艳      | C102 | 高等数学     |    90 |
| S2014 | 刘艳      | C103 | 线性代数     |    85 |
| S2014 | 刘艳      | C104 | 临床医学     |    77 |
| S2014 | 刘艳      | C105 | 传染病学     |    96 |
| S2015 | 刘艳      | C101 | 古代文学     |    69 |
| S2015 | 刘艳      | C102 | 高等数学     |    66 |
| S2015 | 刘艳      | C103 | 线性代数     |    88 |
| S2015 | 刘艳      | C104 | 临床医学     |    69 |
| S2015 | 刘艳      | C105 | 传染病学     |    66 |
| S2016 | 刘若非    | C101 | 古代文学     |    65 |
| S2016 | 刘若非    | C102 | 高等数学     |    69 |
| S2021 | 董雯花    | C102 | 高等数学     |    72 |
| S2021 | 董雯花    | C103 | 线性代数     |    90 |
| S2021 | 董雯花    | C104 | 临床医学     |    90 |
| S2021 | 董雯花    | C105 | 传染病学     |    57 |
| S2022 | 周华建    | C102 | 高等数学     |    88 |
| S2022 | 周华建    | C103 | 线性代数     |    93 |
| S2023 | 特朗普    | C102 | 高等数学     |    68 |
| S2023 | 特朗普    | C103 | 线性代数     |    86 |
| S2024 | 奥巴马    | C102 | 高等数学     |    87 |
| S2024 | 奥巴马    | C103 | 线性代数     |    97 |
| S2025 | 周健华    | C102 | 高等数学     |    61 |
| S2025 | 周健华    | C105 | 传染病学     |    62 |
| S2026 | 张学有    | C102 | 高等数学     |    59 |
| S2026 | 张学有    | C105 | 传染病学     |    48 |
+-------+-----------+------+--------------+-------+
36 rows in set (0.00 sec)-- 表1（交叉表）
+-----------+------------+------------+------------+-------------+-------------+--------+
| s_name    | 古代文学    | 高等数学   | 线性代数    | 临床医学     | 传染病学     | 总分   |
+-----------+------------+------------+------------+-------------+-------------+--------+
| 刘小青    | 67          | 52        | 55          | 86          | 87          |    347 |
| 刘艳      | 69          | 156       | 173         | 146         | 16          |    706 |
| 刘若非    | 65          | 69        | 未选修       | 未选修      | 未选修       |    134 |
| 周健华    | 未选修       | 61        | 未选修       | 未选修      | 62          |    123 |
| 周华建    | 未选修       | 88        | 93          | 未选修      | 未选修       |    181 |
| 奥巴马    | 未选修       | 87        | 97          | 未选修      | 未选修       |    184 |
| 张学有    | 未选修       | 59        | 未选修       | 未选修      | 48          |    107 |
| 张晓刚    | 未选修       | 84        | 未选修       | 未选修      | 90          |    174 |
| 曹梦德    | 未选修       | 97        | 68          | 66          | 68          |    299 |
| 特朗普    | 未选修       | 68        | 86          | 未选修      | 未选修       |    154 |
| 董雯花    | 未选修       | 72        | 90          | 90          | 57          |    309 |
+-----------+-------------+-----------+-------------+-------------+-------------+--------+

二、静态交叉表

生成交叉表的基本方法为：

（1）按照交叉表的行字段进行分组（比如 s_name）。

（2）对要转换为列的行字段（比如 c_name）进行统计，在统计函数中使用 IF 函数对数据进行筛选，生成列字段。

交叉表查询代码如下：

/*
select ifnull(s_name,'总分') 姓名,if(sum(if(c.c_id='C101',score,0))=0,'未选修',sum(if(c.c_id='C101',score,0))) 古代文学,if(sum(if(c.c_id='C102',score,0))=0,'未选修',sum(if(c.c_id='C102',score,0))) 高等数学,if(sum(if(c.c_id='C103',score,0))=0,'未选修',sum(if(c.c_id='C103',score,0))) 线性代数,if(sum(if(c.c_id='C104',score,0))=0,'未选修',sum(if(c.c_id='C104',score,0))) 临床医学,if(sum(if(c.c_id='C105',score,0))=0,'未选修',sum(if(c.c_id='C105',score,0))) 传染病学,sum(score) 总分
from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id
group by s_name with rollup;
*/mysql> select ifnull(s_name,'总分') 姓名,->    if(sum(if(c.c_id='C101',score,0))=0,'未选修',sum(if(c.c_id='C101',score,0))) 古代文学,->    if(sum(if(c.c_id='C102',score,0))=0,'未选修',sum(if(c.c_id='C102',score,0))) 高等数学,->    if(sum(if(c.c_id='C103',score,0))=0,'未选修',sum(if(c.c_id='C103',score,0))) 线性代数,->    if(sum(if(c.c_id='C104',score,0))=0,'未选修',sum(if(c.c_id='C104',score,0))) 临床医学,->    if(sum(if(c.c_id='C105',score,0))=0,'未选修',sum(if(c.c_id='C105',score,0))) 传染病学,->    sum(score) 总分-> from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id-> group by s_name with rollup;
+-----------+-------------+-------------+-------------+-------------+-------------+--------+
| 姓名      | 古代文学     | 高等数学     | 线性代数     | 临床医学    | 传染病学     | 总分   |
+-----------+-------------+-------------+-------------+-------------+-------------+--------+
| 刘小青    | 67           | 52          | 55          | 86          | 87          |    347 |
| 刘艳      | 69           | 156         | 173         | 146         | 162         |    706 |
| 刘若非    | 65           | 69          | 未选修       | 未选修      | 未选修       |    134 |
| 周健华    | 未选修        | 61          | 未选修      | 未选修       | 62          |    123 |
| 周华建    | 未选修        | 88          | 93          | 未选修      | 未选修       |    181 |
| 奥巴马    | 未选修        | 87          | 97          | 未选修      | 未选修       |    184 |
| 张学有    | 未选修        | 59          | 未选修       | 未选修      | 48          |    107 |
| 张晓刚    | 未选修        | 84          | 未选修       | 未选修      | 90          |    174 |
| 曹梦德    | 未选修        | 97          | 68          | 66          | 68          |    299 |
| 特朗普    | 未选修        | 68          | 86          | 未选修       | 未选修      |    154 |
| 董雯花    | 未选修        | 72          | 90          | 90          | 57          |    309 |
| 总分      | 201          | 893         | 662         | 388         | 574         |   2718 |
+-----------+--------------+-------------+-------------+-------------+-------------+--------+
12 rows in set (0.03 sec)

三、动态交叉表

实现动态交叉表的基本思路为：

（1）使用 GROUP_CONCAT 函数取出要进行行列转换的列中的所有数据。

（2）使用 CONCAT 函数拼接成和静态交叉表查询相同的 SELECT 语句。

（3）使用预处理（prepare）命令执行，得到最终的查询结果。

仍然以上面的 student、course、score 三个表为例，动态生成交叉表。

1、取出要进行行列转换的列中的数据

使用 GROUP_CONCAT 函数取出要进行行列转换的列（c_name）中的数据。

/*
select group_concat(distinct c.c_name)
from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id;
*/mysql> select group_concat(distinct c.c_name)-> from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id;
+------------------------------------------------------------------+
| group_concat(distinct c.c_name)                                  |
+------------------------------------------------------------------+
| 临床医学,传染病学,古代文学,线性代数,高等数学                     |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

2、使用 CONCAT 函数生成拼接字符串

/*
说明：为 ' 为转义字符，把单引号（'）拼接到字符串中
SELECT GROUP_CONCAT(DISTINCT (CONCAT('IF(SUM(IF(c_name='',c_name,'',score,0))=0,'未选修',SUM(IF(c_name='',c_name,'',score,0))) ',c_name))
)
INTO @sql_str
FROM student s,course c,score sc, (SELECT @sql_str) a
where s.s_id=sc.s_id and c.c_id=sc.c_id;
*/mysql> SELECT GROUP_CONCAT(DISTINCT ->     (CONCAT('IF(SUM(IF(c_name='',c_name,'',score,0))=0,'未选修',SUM(IF(c_name='',c_name,'',score,0))) ',c_name))-> )-> INTO @sql_str-> FROM student s,course c,score sc, (SELECT @sql_str) a-> where s.s_id=sc.s_id and c.c_id=sc.c_id;
Query OK, 1 row affected (0.00 sec)mysql> SELECT @sql_str;
+-----------------------------------------------------------+| 
@sql_str 
|+----------------------------------------------------------+| 
IF(SUM(IF(c_name='临床医学',score,0))=0,'未选修',SUM(IF(c_name='临床医学',score,0))) 临床医学,
IF(SUM(IF(c_name='传染病学',score,0))=0,'未选修',SUM(IF(c_name='传染病学',score,0))) 传染病学,
IF(SUM(IF(c_name='古代文学',score,0))=0,'未选修',SUM(IF(c_name='古代文学',score,0))) 古代文学,
IF(SUM(IF(c_name='线性代数',score,0))=0,'未选修',SUM(IF(c_name='线性代数',score,0))) 线性代数,
IF(SUM(IF(c_name='高等数学',score,0))=0,'未选修',SUM(IF(c_name='高等数学',score,0))) 高等数学
|+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

3、拼接完整的 SELECT 语句

/*
SELECT GROUP_CONCAT(DISTINCT (CONCAT('IF(SUM(IF(c_name='',c_name,'',score,0))=0,'未选修',SUM(IF(c_name='',c_name,'',score,0))) ',c_name))
)
INTO @sql_str
FROM student s,course c,score sc, (SELECT @sql_str) a
where s.s_id=sc.s_id and c.c_id=sc.c_id;set @sql_str =
CONCAT('SELECT IFNULL(s_name, '总分') 姓名,',@sql_str,',SUM(score) 总分 FROM student s,course c,score scwhere s.s_id=sc.s_id and c.c_id=sc.c_id GROUP BY s.s_name WITH ROLLUP;');
*/mysql> SELECT GROUP_CONCAT(DISTINCT ->     (CONCAT('IF(SUM(IF(c_name='',c_name,'',score,0))=0,'未选修',SUM(IF(c_name='',c_name,'',score,0))) ',c_name))-> )-> INTO @sql_str-> FROM student s,course c,score sc, (SELECT @sql_str) a-> where s.s_id=sc.s_id and c.c_id=sc.c_id;
Query OK, 1 row affected (0.00 sec)mysql> set @sql_str =-> CONCAT('SELECT IFNULL(s_name, '总分') 姓名,',->        @sql_str,->        ',SUM(score) 总分 '>        FROM student s,course c,score sc'>        where s.s_id=sc.s_id and c.c_id=sc.c_id '>        GROUP BY s.s_name WITH ROLLUP;');
Query OK, 0 rows affected (0.00 sec)'mysql> select @sql_str;
+---------------------------------------------------------------+| 
@sql_str
|+-------------------------------------------------------------+| 
SELECT IFNULL(s_name, '总分') 姓名,IF(SUM(IF(c_name='临床医学',score,0))=0,'未选修',SUM(IF(c_name='临床医学',score,0))) 临床医学,IF(SUM(IF(c_name='传染病学',score,0))=0,'未选修',SUM(IF(c_name='传染病学',score,0))) 传染病学,IF(SUM(IF(c_name='古代文学',score,0))=0,'未选修',SUM(IF(c_name='古代文学',score,0))) 古代文学,IF(SUM(IF(c_name='线性代数',score,0))=0,'未选修',SUM(IF(c_name='线性代数',score,0))) 线性代数,IF(SUM(IF(c_name='高等数学',score,0))=0,'未选修',SUM(IF(c_name='高等数学',score,0))) 高等数学,SUM(score) 总分        
FROM student s,course c,score sc
where s.s_id=sc.s_id and c.c_id=sc.c_id 
GROUP BY s.s_name WITH ROLLUP;
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

4、使用预处理命令执行拼接好的字符串

/*
-- PREPARE 语句语法说明：
PREPARE statement_name FROM SQL字符串;
-- 执行预处理语句
EXECUTE statement_name; 
-- 删除预处理语句定义
DEALLOCATE | DROP PREPARE statement_name;
*/mysql> PREPARE stmt from @sql_str;
Query OK, 0 rows affected (0.00 sec)
Statement preparedmysql> EXECUTE stmt;
+-----------+-------------+-------------+-------------+--------------+-------------+--------+
| 姓名      | 临床医学     | 传染病学     | 古代文学     | 线性代数     | 高等数学     | 总分   |
+-----------+-------------+-------------+-------------+--------------+-------------+--------+
| 刘小青    | 86          | 87           | 67          | 55           | 52          |    347 |
| 刘艳      | 146         | 162          | 69          | 173          | 156         |    706 |
| 刘若非    | 未选修       | 未选修       | 65          | 未选修        | 69          |    134 |
| 周健华    | 未选修       | 62           | 未选修      | 未选修        | 61          |    123 |
| 周华建    | 未选修       | 未选修       | 未选修       | 93           | 88          |    181 |
| 奥巴马    | 未选修       | 未选修       | 未选修       | 97           | 87          |    184 |
| 张学有    | 未选修       | 48           | 未选修       | 未选修       | 59          |    107 |
| 张晓刚    | 未选修       | 90           | 未选修       | 未选修       | 84          |    174 |
| 曹梦德    | 66           | 68          | 未选修       | 68           | 97          |    299 |
| 特朗普    | 未选修       | 未选修       | 未选修       | 86           | 68          |    154 |
| 董雯花    | 90          | 57           | 未选修       | 90           | 72          |    309 |
| 总分      | 388         | 574          | 201         | 662          | 893         |   2718 |
+-----------+-------------+--------------+-------------+--------------+-------------+--------+
12 rows in set (0.00 sec)mysql> DEALLOCATE prepare stmt;
Query OK, 0 rows affected (0.00 sec)

5、把交叉表查询定义为存储过程

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

代码如下：

DELIMITER &&
CREATE PROCEDURE sp_crosstable() reads SQL dataBEGINSET @sql_str = NULL;SELECT GROUP_CONCAT(DISTINCT (CONCAT('IF(SUM(IF(c_name='',c_name,'',score,0))=0,'未选修',SUM(IF(c_name='',c_name,'',score,0))) ',c_name)))INTO @sql_strFROM student s,course c,score sc, (SELECT @sql_str) awhere s.s_id=sc.s_id and c.c_id=sc.c_id;set @sql_str =CONCAT('SELECT IFNULL(s_name, '总分') 姓名,',@sql_str,',SUM(score) 总分 FROM student s,course c,score scwhere s.s_id=sc.s_id and c.c_id=sc.c_id GROUP BY s.s_name WITH ROLLUP;');PREPARE stmt from @sql_str;EXECUTE stmt;DEALLOCATE prepare stmt;
END &&
DELIMITER ;

执行以上代码，生成存储过程：

mysql> DELIMITER &&
mysql> CREATE PROCEDURE sp_crosstable() reads SQL data->     BEGIN->     SET @sql_str = NULL;-> ->     SELECT GROUP_CONCAT(DISTINCT ->         (CONCAT('IF(SUM(IF(c_name='',c_name,'',score,0))=0,'未选修',SUM(IF(c_name='',c_name,'',score,0))) ',c_name))->         )->     INTO @sql_str->     FROM student s,course c,score sc, (SELECT @sql_str) a->     where s.s_id=sc.s_id and c.c_id=sc.c_id;-> ->     set @sql_str =->     CONCAT('SELECT IFNULL(s_name, '总分') 姓名,',->        @sql_str,->        ',SUM(score) 总分 '>        FROM student s,course c,score sc'>        where s.s_id=sc.s_id and c.c_id=sc.c_id '>        GROUP BY s.s_name WITH ROLLUP;');-> ->     PREPARE stmt from @sql_str;->     EXECUTE stmt;->     DEALLOCATE prepare stmt;-> END &&
Query OK, 0 rows affected (0.10 sec)mysql> DELIMITER ;mysql> select name from mysql.proc where db='mydb';
+---------------+
| name          |
+---------------+
| sp_crosstable |
+---------------+
1 row in set (0.01 sec)

执行存储过程，生成交叉表：

mysql> CALL sp_crosstable;
+-----------+--------------+--------------+--------------+--------------+--------------+--------+
| 姓名      | 临床医学     | 传染病学     | 古代文学     | 线性代数     | 高等数学     | 总分   |
+-----------+--------------+--------------+--------------+--------------+--------------+--------+
| 刘小青    | 86           | 87           | 67           | 55           | 52           |    347 |
| 刘艳      | 146          | 162          | 69           | 173          | 156          |    706 |
| 刘若非    | 未选修       | 未选修       | 65           | 未选修       | 69           |    134 |
| 周健华    | 未选修       | 62           | 未选修       | 未选修       | 61           |    123 |
| 周华建    | 未选修       | 未选修       | 未选修       | 93           | 88           |    181 |
| 奥巴马    | 未选修       | 未选修       | 未选修       | 97           | 87           |    184 |
| 张学有    | 未选修       | 48           | 未选修       | 未选修       | 59           |    107 |
| 张晓刚    | 未选修       | 90           | 未选修       | 未选修       | 84           |    174 |
| 曹梦德    | 66           | 68           | 未选修       | 68           | 97           |    299 |
| 特朗普    | 未选修       | 未选修       | 未选修       | 86           | 68           |    154 |
| 董雯花    | 90           | 57           | 未选修       | 90           | 72           |    309 |
| 总分      | 388          | 574          | 201          | 662          | 893          |   2718 |
+-----------+--------------+--------------+--------------+--------------+--------------+--------+
12 rows in set (0.03 sec)Query OK, 0 rows affected (0.03 sec)

数据库系统原理与应用教程（052）—— MySQL 的数据完整性（十四）：交叉表查询（行列转换）

数据库系统原理与应用教程（052）—— MySQL 的数据完整性（十四）：交叉表查询（行列转换）

目录

一、交叉表的形式

二、静态交叉表

三、动态交叉表

1、取出要进行行列转换的列中的数据

2、使用 CONCAT 函数生成拼接字符串

3、拼接完整的 SELECT 语句

4、使用预处理命令执行拼接好的字符串

5、把交叉表查询定义为存储过程

潜渊症武器选择指南各类型武器特性一览

宝可梦晶灿钻石明亮珍珠金刚宝珠获取方法介绍

最新文章

摩托罗拉 Razr 50 / Ultra 5G 手机更多渲染图曝光：至高搭骁龙 8s Gen 3，899 欧元起

中年阿里，贵在折腾

华为 PC 新品擎云 W515x / 585x 规格公布，首发搭载麒麟 9000C 处理器

小米 Redmi Pad Pro 平板 8GB+128GB 版本限时优惠价 1499 元，已购用户差价全补

买家反馈华为nova 12 Ultra星耀版搭载麒麟9010L处理器

WPS如何设置从图片获取文字 WPS设置从图片获取文字的方法

Word如何更改每页固定行数 Word更改每页固定行数的方法

Word如何设置行号与正文距离 Word设置行号与正文距离的方法

路由器提示光信号是什么无线路由器光信号灯闪烁是什么原因

电信光猫wifi怎么设置路由器电信的光猫怎么连接路由器

点击排行

MOZA R3 伺服直驱赛车模拟器套装 5 月 17 日发布：Xbox 官方授权、3.9 牛米扭矩

世界最大功率窄轨交流传动内燃机车上线，中国首次出口塞拉利昂

美国多个特斯拉充电站遭窃：电缆被剪，动机成谜

如何展示网站操作流程（如何展示网站操作流程视频）

HKC G27H2 27 英寸显示器开售：2K 180Hz IPS 屏、小巧底座，999 元

标签

关注我们么么哒！

数据库系统原理与应用教程（052）—— MySQL 的数据完整性（十四）：交叉表查询（行列转换）

数据库系统原理与应用教程（052）—— MySQL 的数据完整性（十四）：交叉表查询（行列转换）

目录

一、交叉表的形式

二、静态交叉表

三、动态交叉表

1、取出要进行行列转换的列中的数据

2、使用 CONCAT 函数生成拼接字符串

3、拼接完整的 SELECT 语句

4、使用预处理命令执行拼接好的字符串

5、把交叉表查询定义为存储过程

潜渊症武器选择指南 各类型武器特性一览

宝可梦晶灿钻石明亮珍珠金刚宝珠获取方法介绍

最新文章

摩托罗拉 Razr 50 / Ultra 5G 手机更多渲染图曝光：至高搭骁龙 8s Gen 3，899 欧元起

点击排行

MOZA R3 伺服直驱赛车模拟器套装 5 月 17 日发布：Xbox 官方授权、3.9 牛米扭矩

世界最大功率窄轨交流传动内燃机车上线，中国首次出口塞拉利昂

美国多个特斯拉充电站遭窃：电缆被剪，动机成谜

如何展示网站操作流程（如何展示网站操作流程视频）

HKC G27H2 27 英寸显示器开售：2K 180Hz IPS 屏、小巧底座，999 元

标签

关注我们 么么哒！

关注我们的公众号

潜渊症武器选择指南各类型武器特性一览

关注我们么么哒！