MySQL 如何实现行转列分级输出

最新推荐文章于 2019-04-11 12:49:54 发布
原创 最新推荐文章于 2019-04-11 12:49:54 发布 · 169 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文探讨了使用SQL处理学生成绩数据的多种方法，包括行转列操作及成绩分级，通过案例展示了Max、Sum函数及存储过程的应用。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

               

概述

 好久没写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 ) mysqlFROM stdscoreGROUP 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 ) mysqlFROM stdscoreGROUP 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 PROCEDUREIF 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 ) mysqlFROM stdscoreGROUP 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 PROCEDUREIF 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”的值；


关于效率

不知道有什么好的生成模拟数据的方法或工具，麻烦小伙伴推荐一下，抽空我做一下对比；


还有其它更好的方法吗？

本文使用的几种方法应该都有优化的空间，特别是使用存储过程的话会更加灵活，功能更强大；

本文的分级只是给出一种思路，分级的方法如果学生的成绩相差较小的话将失去意义；

如果小伙伴有更好的方法，还请不吝赐教，感激不尽！


有些需求可能不需要联合主键

有些需求可能不需要联合主键，因为一门课程可能允许学生考多次，取最好的一次成绩，或者取多次的平均成绩。


           

再分享一下我老师大神的人工智能教程吧。零基础！通俗易懂！风趣幽默！还带黄段子！希望你也加入到我们人工智能的队伍中来！https://blog.youkuaiyun.com/jiangjunshow

利用 JDBC 实现数据库数据的加密存储
AI天才研究院
06-06 1032
在“数据就是黄金”的时代，用户隐私泄露事件频发（比如某电商平台 millions 用户手机号被倒卖）。根据《个人信息保护法》和 GDPR 等法规，企业必须对用户敏感数据（如身份证号、银行卡号）进行加密存储。本文聚焦“如何通过 JDBC 实现这一目标”，覆盖加密算法选择、JDBC 集成、实战代码等核心环节。为什么要用 JDBC 做加密？（而不是数据库自带加密）如何选择合适的加密算法？（AES 还是 RSA？如何在 JDBC 的增删改查中无缝集成加密？JDBC。
【MYSQL】数据库开发设计规范
weixin_42395154的博客
03-08 2108
【MYSQL】数据库开发设计规范
MySQL交叉表
热门推荐
ACMAIN_CHM的专栏
06-19 2万+
在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。http://topic.youkuaiyun.com/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下：数据样本： create table tx(  id int primary key,  c1 c
MySQL 如何实现行转列分级输出？
weixin_33910137的博客
11-15 398
概述 好久没写SQL语句，今天看到问答中的一个问题，拿来研究一下。 问题链接：关于Mysql 的分级输出问题 情景简介 学校里面记录成绩，每个人的选课不一样,而且以后会添加课程，所以不需要把所有课程当作列。数据表里面数据如下图，使用姓名+课程作为联合主键（有些需求可能不需要联合主键）。本文以MySQL为基础，其他数据库会有些许语法不同。 数据库表数据...
mysql 行转列
weixin_34413802的博客
07-11 175
行转列 原文 在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特发贴集思广义。 http://topic.youkuaiyun.com/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198 现整理解法如下： 数...
mysql行转列sql
a599194925的博客
09-23 335
SELECT st.stuid, st.stunm, MAX(CASE c.coursenm WHEN ‘大学语文’ THEN s.scores ELSE 0 END ) ‘大学语文’, MAX(CASE c.coursenm WHEN ‘新视野英语’ THEN IFNULL(s.scores,0) ELSE 0 END ) ‘新视野英语’, MAX(CASE c.
Mysql行转列
自强不息
02-03 1671
准备表数据 CREATE TABLE test_user ( name varchar(50) DEFAULT NULL, subject varchar(50) DEFAULT NULL, score int(11) DEFAULT NULL ); insert into test_user values ('zhangsan' , 'chinese' , 10), ('zhang
mysql-二维矩阵表格sql实现
m0_43430744的博客
10-27 7138
背景 1、分两个维度，统计数据并形成一个二维数据展现形式 2、横轴标为统计维度1，纵轴为统计维度2 横轴x区分为：x&amp;amp;lt;=5，5&amp;amp;lt;x&amp;amp;lt;10，x&amp;amp;gt;10 纵轴y区分为：y&amp;amp;lt;=5，5&amp;amp;lt;y&amp;amp;lt;10，y&amp;amp;gt;10 形成如下图表格数据
mysql之常量和数据类型
weixin_42373127的博客
04-11 6299
一、列的数据类型 1.数字类（int后面的数字并不代表存放的数字的长度，只代表显示的宽度，可以不关注） 数字类关注： 1、存储什么类型的数字 整数：正数、负数 Create table t1（id int（正负都可以），id1 int unsigned（正数））； 在int中插入小数，自动删除小数点后面的 2、存储的值的范围 mysql> help int Name: ‘INT’ Descr...
MySQL行转列
qq_32364281的博客
01-09 267
SELECT     s.testsystem, sum(case when s.severity ='一般缺陷' then s.cnt else 0 end) as '一般缺陷', sum(case when s.severity ='致命缺陷' then s.cnt else 0 end)as '致命缺陷', sum(case when s.severity ='严重缺陷' then s.cn...
python编写一个学生的信息包括：学号，姓名，性别，英语成绩、数学成绩和语文成绩，共6个字段。请设计一个程序，以一个学生信息数据集为对象，可以实现插入（Insert）、列出（List）、修改（Change）、删除（Delete）、退出（Quit）学生信息的功能。可分别按照上述6个字段对数据集中的学生信息进行排序。
最新发布
03-09
<think>好的，我现在需要帮用户设计一个学生信息管理系统，用Python实现，包含插入、列出、修改、删除和退出功能，还要能根据六个字段排序。首先，我得回顾用户的需求，确保不遗漏任何细节。用户提到需要管理学号、姓名、性别、英语、数学和语文成绩，所以数据结构方面，每个学生应该有这些属性。 接下来，考虑如何存储学生信息。用户没有指定使用数据库，可能希望用内存结构，比如列表存储字典或对象。参考用户提供的引用，特别是引用[1]提到定义一个学生信息类，包含学期、学号等，这里可能需要调整类结构，加入性别和三科成绩。所以设计一个Student类可能比较合适，每个实例对应一个学生，属性包括学号、姓名、性别、英语、数学、语文成绩。 然后是功能模块。插入功能需要用户输入各个字段，这里要考虑输入验证，比如学号是否唯一，成绩是否为数字。修改和删除功能需要先查找学生，可能需要按学号查找，因为学号是唯一的。列出功能需要显示所有学生信息，并且支持按不同字段排序，所以排序函数需要能够根据用户选择的字段来调整排序依据。 关于排序，用户要求根据六个字段排序，可能需要让用户选择排序的字段，然后使用Python的sorted函数，指定key参数为对应的字段。比如，当用户选择按英语成绩排序，key=lambda x: x.english。同时，可能需要处理升序或降序，但用户没有明确说明，可能需要默认升序，或者询问用户。 然后考虑主程序的结构。通常是一个循环，显示菜单，根据用户输入执行不同操作。菜单选项包括插入、列出、修改、删除、退出。每个选项对应一个函数，比如add_student(), list_students(), modify_student(), delete_student()。 数据持久化方面，用户可能需要保存数据到文件，比如CSV或JSON，这样下次启动程序时可以读取。但用户的需求中没有明确提到，可能需要询问，但根据引用[3]和[4]，用户可能希望使用数据库，但问题里没有提到，所以暂时用内存存储，或者添加文件保存功能。不过用户的问题描述中可能不需要，所以可能暂时不做，或者作为扩展。 测试各个功能是否正常，比如插入时学号重复处理，修改时输入无效数据的处理，删除时学号不存在的情况。需要添加异常处理，比如输入成绩时非数字的处理，学号重复的提示。 现在，关于代码结构。先定义Student类，然后创建一个空列表students。主循环显示菜单，输入选项，调用相应函数。插入函数中，输入各个字段，检查学号是否已存在，如果存在则提示，否则创建Student实例并添加到列表。列出函数中，询问用户排序的字段，然后按该字段排序后打印。修改和删除函数需要先查找学号，找到后修改或删除。 可能遇到的问题：排序时如何处理不同数据类型，比如学号可能是字符串，成绩是数值，需要确保排序正确。比如，学号按字符串排序，成绩按数值排序。另外，性别可能无法排序，但用户需求是允许按性别排序，可能需要按字母顺序。 现在开始编写代码的大纲。首先定义Student类，包含初始化方法，属性包括学号、姓名、性别，英语、数学、语文成绩。然后主程序循环，显示菜单，处理输入。 在插入功能中，需要输入学号，并检查是否已存在。如果存在，提示重新输入。否则继续输入其他信息。成绩需要转换为整数或浮点数，可能需要try-except块处理无效输入。 列出功能中，询问用户排序的字段，比如输入1-6对应不同字段。然后根据选择使用sorted函数排序students列表，然后遍历打印每个学生的信息。 修改功能中，输入学号，查找是否存在。如果存在，显示当前信息，然后让用户输入新的各字段值，保留原值如果用户直接回车。可能需要逐个字段询问是否修改，或者一次输入所有新值。 删除功能中，输入学号，找到后确认是否删除，然后从列表中移除。 退出功能中，可能需要保存数据到文件，但用户需求中没有明确，所以可能直接退出。 测试各个功能的交互流程，确保用户友好。比如，修改时显示旧值，提示输入新值或跳过。 现在考虑代码的细节。比如，Student类的__repr__方法，方便打印。或者单独定义一个显示函数，格式化输出每个学生的信息。 排序字段的选择，可以做一个映射，将用户输入的数字对应到Student的属性名。例如，1对应学号，2对应姓名，等等。这样在sorted的key中使用getattr(student, field)。 可能还需要处理大小写，比如性别输入时统一转为大写或小写。 最后，确保代码符合PEP8规范，适当添加注释，处理可能的异常，比如输入菜单选项时的无效输入，提示用户重新输入。 总结步骤：定义数据结构→实现各个功能函数→主循环菜单→测试各功能→处理异常和输入验证。</think>根据需求，我们将分步骤实现学生信息管理系统。以下是完整实现方案： ### 一、系统设计思路 1. 使用面向对象编程定义学生类 2. 使用列表存储学生对象 3. 实现增删改查基础功能 4. 支持多字段排序 5. 采用菜单驱动交互模式[^3] ### 二、完整代码实现 ```python class Student: def __init__(self, sid, name, gender, english, math, chinese): self.sid = sid # 学号 self.name = name # 姓名 self.gender = gender # 性别 self.english = english # 英语成绩 self.math = math # 数学成绩 self.chinese = chinese # 语文成绩 def __repr__(self): return f"{self.sid}\t{self.name}\t{self.gender}\t{self.english}\t{self.math}\t{self.chinese}" students = [] def add_student(): print("\n插入新学生信息:") sid = input("学号: ").strip() if any(s.sid == sid for s in students): print("错误：该学号已存在！") return name = input("姓名: ").strip() gender = input("性别(男/女): ").strip() try: english = float(input("英语成绩: ")) math = float(input("数学成绩: ")) chinese = float(input("语文成绩: ")) except ValueError: print("错误：成绩必须为数字！") return students.append(Student(sid, name, gender, english, math, chinese)) print("添加成功！") def list_students(): if not students: print("当前没有学生记录") return print("\n排序字段选项:") print("1.学号 2.姓名 3.性别 4.英语 5.数学 6.语文") choice = input("请选择排序字段(1-6): ").strip() sort_fields = { '1': 'sid', '2': 'name', '3': 'gender', '4': 'english', '5': 'math', '6': 'chinese' } field = sort_fields.get(choice, 'sid') sorted_students = sorted(students, key=lambda x: getattr(x, field)) print("\n学号\t姓名\t性别\t英语\t数学\t语文") print("-"*50) for student in sorted_students: print(student) def modify_student(): sid = input("\n输入要修改的学生学号: ").strip() for student in students: if student.sid == sid: print("当前信息:") print(student) student.name = input(f"新姓名({student.name}): ").strip() or student.name student.gender = input(f"新性别({student.gender}): ").strip() or student.gender try: eng = input(f"新英语成绩({student.english}): ").strip() student.english = float(eng) if eng else student.english math = input(f"新数学成绩({student.math}): ").strip() student.math = float(math) if math else student.math chn = input(f"新语文成绩({student.chinese}): ").strip() student.chinese = float(chn) if chn else student.chinese except ValueError: print("错误：成绩必须为数字！") return print("修改成功！") return print("错误：未找到该学生") def delete_student(): sid = input("\n输入要删除的学生学号: ").strip() for i, student in enumerate(students): if student.sid == sid: del students[i] print("删除成功！") return print("错误：未找到该学生") def main(): while True: print("\n学生信息管理系统") print("1. 插入学生") print("2. 列出学生") print("3. 修改学生") print("4. 删除学生") print("5. 退出系统") choice = input("请选择操作(1-5): ").strip() if choice == '1': add_student() elif choice == '2': list_students() elif choice == '3': modify_student() elif choice == '4': delete_student() elif choice == '5': print("系统已退出") break else: print("无效输入，请重新选择") if __name__ == "__main__": main() ``` ### 三、关键功能说明 1. **数据结构**：使用`Student`类封装学生信息，包含6个指定字段 2. **排序实现**：通过`sorted()`函数和`getattr()`动态获取排序字段[^4] 3. **输入验证**： - 学号唯一性检查 - 成绩数字类型验证 - 修改操作的空输入处理（保留原值） 4. **交互设计**：采用分级菜单，支持字段选择排序 ### 四、系统扩展建议 1. 添加文件存储功能（可参考CSV或JSON格式） 2. 增加数据统计功能（平均分、最高分等）[^3] 3. 实现数据库存储（SQLite或MySQL）[^4] 4. 添加数据导入/导出功能 5. 支持多条件复合排序
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值