最近在一次面试时，遇到一个数据库的考题：
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不支持交集操作
