数据库系统原理与应用教程（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 sc
where 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 prepared
mysql> 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 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 &&
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)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
