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