概述
好久没写SQL语句,今天看到问答中的一个问题,拿来研究一下。
问题链接:关于Mysql 的分级输出问题
情景简介
学校里面记录成绩,每个人的选课不一样,而且以后会添加课程,所以不需要把所有课程当作列。数据表里面数据如下图,使用姓名+课程作为联合主键(有些需求可能不需要联合主键)。本文以MySQL为基础,其他数据库会有些许语法不同。
数据库表数据:
处理后的结果(行转列):
方法一:
这里可以使用Max,也可以使用Sum;
注意第二张图,当有学生的某科成绩缺失的时候,输出结果为Null;
SELECT SNAME, MAX( CASE CNAME WHEN 'JAVA' THEN SCORE END ) JAVA, MAX( CASE CNAME WHEN 'mysql' THEN SCORE END ) mysql FROM stdscore GROUP BY SNAME;
可以在第一个Case中加入Else语句解决这个问题:
SELECT SNAME, MAX( CASE CNAME WHEN 'JAVA' THEN SCORE ELSE 0 END ) JAVA, MAX( CASE CNAME WHEN 'mysql' THEN SCORE ELSE 0 END ) mysql FROM stdscore GROUP BY SNAME;
方法二:
SELECT DISTINCT a.sname, (SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='JAVA' ) AS 'JAVA', (SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='mysql' ) AS 'mysql' FROM stdscore a
方法三:
DROP PROCEDURE IF EXISTS sp_score; DELIMITER && CREATE PROCEDURE sp_score () BEGIN #课程名称 DECLARE cname_n VARCHAR (20) ; #所有课程数量 DECLARE count INT ; #计数器 DECLARE i INT DEFAULT 0 ; #拼接SQL字符串 SET @s = 'SELECT sname' ; SET count = ( SELECT COUNT(DISTINCT cname) FROM stdscore ) ; WHILE i < count DO SET cname_n = ( SELECT cname FROM stdscore GROUP BY CNAME LIMIT i, 1 ) ; SET @s = CONCAT( @s, ', SUM(CASE cname WHEN ', '\'', cname_n, '\'', ' THEN score ELSE 0 END)', ' AS ', '\'', cname_n, '\'' ) ; SET i = i + 1 ; END WHILE ; SET @s = CONCAT( @s, ' FROM stdscore GROUP BY sname' ) ; #用于调试 #SELECT @s; PREPARE stmt FROM @s ; EXECUTE stmt ; END&& CALL sp_score () ;
处理后的结果(行转列)分级输出:
方法一:
这里可以使用Max,也可以使用Sum;
注意第二张图,当有学生的某科成绩缺失的时候,输出结果为Null;
SELECT SNAME, MAX( CASE CNAME WHEN 'JAVA' THEN ( CASE WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN '优秀' WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN '良好' WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN '普通' ELSE '较差' END ) END ) JAVA, MAX( CASE CNAME WHEN 'mysql' THEN ( CASE WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN '优秀' WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN '良好' WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN '普通' ELSE '较差' END ) END ) mysql FROM stdscore GROUP BY SNAME;
方法二:
SELECT DISTINCT a.sname, (SELECT ( CASE WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN '优秀' WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN '良好' WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN '普通' ELSE '较差' END ) FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='JAVA' ) AS 'JAVA', (SELECT ( CASE WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN '优秀' WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN '良好' WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN '普通' ELSE '较差' END ) FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='mysql' ) AS 'mysql' FROM stdscore a方法三:
DROP PROCEDURE IF EXISTS sp_score; DELIMITER && CREATE PROCEDURE sp_score () BEGIN #课程名称 DECLARE cname_n VARCHAR (20) ; #所有课程数量 DECLARE count INT ; #计数器 DECLARE i INT DEFAULT 0 ; #拼接SQL字符串 SET @s = 'SELECT sname' ; SET count = ( SELECT COUNT(DISTINCT cname) FROM stdscore ) ; WHILE i < count DO SET cname_n = ( SELECT cname FROM stdscore GROUP BY CNAME LIMIT i, 1 ) ; SET @s = CONCAT( @s, ', MAX(CASE cname WHEN ', '\'', cname_n, '\'', ' THEN ( CASE WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') > 20 THEN \'优秀\' WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') > 10 THEN \'良好\' WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') >= 0 THEN \'普通\' ELSE \'较差\' END ) END)', ' AS ', '\'', cname_n, '\'' ) ; SET i = i + 1 ; END WHILE ; SET @s = CONCAT( @s, ' FROM stdscore GROUP BY sname' ) ; #用于调试 #SELECT @s; PREPARE stmt FROM @s ; EXECUTE stmt ; END&& CALL sp_score ();
几种方法比较分析
第一种使用了分组,对每个课程分别处理。第二种方法使用了表连接。
第三种使用了存储过程,实际上可以是第一种或第二种方法的动态化,先计算出所有课程的数量,然后对每个分组进行课程查询。这种方法的一个最大的好处是当新增了一门课程时,SQL语句不需要重写。
小结
关于行转列和列转行这个概念似乎容易弄混,有人把行转列理解为列转行,有人把列转行理解为行转列;
这里做个定义:
行转列:把表中特定列(如本文中的:CNAME)的数据去重后做为列名(如查询结果行中的“JAVA,mysql”,处理后是做为列名输出);
列转行:可以说是行转列的反转,把表中特定列(如本文处理结果中的列名“JAVA,mysql”)做为每一行数据对应列“CNAME”的值;
关于效率
不知道有什么好的生成模拟数据的方法或工具,麻烦小伙伴推荐一下,抽空我做一下对比;
还有其它更好的方法吗?
本文使用的几种方法应该都有优化的空间,特别是使用存储过程的话会更加灵活,功能更强大;
本文的分级只是给出一种思路,分级的方法如果学生的成绩相差较小的话将失去意义;
如果小伙伴有更好的方法,还请不吝赐教,感激不尽!
有些需求可能不需要联合主键
有些需求可能不需要联合主键,因为一门课程可能允许学生考多次,取最好的一次成绩,或者取多次的平均成绩。

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SublimeText3 English version
Recommended: Win version, supports code prompts!

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)
