最近在一次面试时,遇到一个数据库的考题:
1、求出所有分数均大于80分的名字
2、求出所有学生的总成绩并由高到低排序
当时看到这题就懵了,第一感觉这个表设计有问题,无论sql语句怎么写,也求不出结果。
也想到了要把数据表转换下,即行转列,但是不会写sql呀。
正解如下:(参考了这篇文章https://blog.csdn.net/jx_870915876/article/details/52403472)
行转列的sql语句
SELECT name , MAX(CASE kecheng WHEN '数学' THEN fenshu ELSE 0 END ) 数学, MAX(CASE kecheng WHEN '语文' THEN fenshu ELSE 0 END ) 语文, MAX(CASE kecheng WHEN '英语' THEN fenshu ELSE 0 END ) 英语 FROM tablename GROUP BY NAME;
|
转换后的表结构如下
这样就好求解了吧 :))
1、sql
SELECT name from (SELECT name , MAX(CASE kecheng WHEN '数学' THEN fenshu ELSE 0 END ) 数学, MAX(CASE kecheng WHEN '语文' THEN fenshu ELSE 0 END ) 语文, MAX(CASE kecheng WHEN '英语' THEN fenshu ELSE 0 END ) 英语 FROM tablename GROUP BY NAME) as a where a.`数学`>80 AND a.`英语`>80 and a.`语文`>80;
|
2、sql语句
SELECT *, sum(a.数学+a.语文+a.英语) as total from (SELECT name , MAX(CASE kecheng WHEN '数学' THEN fenshu ELSE 0 END ) 数学, MAX(CASE kecheng WHEN '语文' THEN fenshu ELSE 0 END ) 语文, MAX(CASE kecheng WHEN '英语' THEN fenshu ELSE 0 END ) 英语 FROM tablename GROUP BY NAME) as a GROUP BY a.name ORDER BY total DESC
|
补充:
如果使用oracle就可以直接写sql语句
SELECT a.name FROM tablename a where fenshu>=80 AND kecheng='语文' InterSect SELECT b.name FROM tablename b where fenshu>=80 AND kecheng='数学' InterSect SELECT c.name FROM tablename c where fenshu>=80 AND kecheng='英语'
|
mysql不支持交集操作