概述
好久没写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”的值;
关于效率
不知道有什么好的生成模拟数据的方法或工具,麻烦小伙伴推荐一下,抽空我做一下对比;
还有其它更好的方法吗?
本文使用的几种方法应该都有优化的空间,特别是使用存储过程的话会更加灵活,功能更强大;
本文的分级只是给出一种思路,分级的方法如果学生的成绩相差较小的话将失去意义;
如果小伙伴有更好的方法,还请不吝赐教,感激不尽!
有些需求可能不需要联合主键
有些需求可能不需要联合主键,因为一门课程可能允许学生考多次,取最好的一次成绩,或者取多次的平均成绩。

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.


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

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

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

SublimeText3 Chinese version
Chinese version, very easy to use

Dreamweaver CS6
Visual web development tools

Notepad++7.3.1
Easy-to-use and free code editor

WebStorm Mac version
Useful JavaScript development tools
