最近研究SQL语句的时候,遇到一道面试题,觉得很有意思,在这里将目前的解决办法整理出来,作为分享。
题目要求:
已知一张成绩表如下:
+--+-----+-------+-------+
| id|name| couname | price |
+--+-----+-------+-----+
| 1 | 小明 | 语文 | 83 |
| 2 | 小明 | 数学 | 70 |
| 3 | 小明 | 英语 | 80 |
| 4 | 小红 | 语文 | 70 |
| 5 | 小红 | 数学 | 80 |
| 6 | 小红 | 英语 | 82 |
| 7 | 小黑 | 语文 | 60 |
| 8 | 小黑 | 数学 | 75 |
| 9 | 小黑 | 英语 | 82 |
+--+-----+-------+-----+
要求用一句话得出所有科目均大于60分的学生姓名,这里应当采用对姓名做分组进行判断,代码如下:
select a.name from (
select min(price) as price,name
from temptable
group by name
) as a
where price > 60;
结果为:
+------+
| name |
+------+
| 小明 |
| 小红 |
+------+
那么如果要将这个表重新组合成一行展示,即表头元素为姓名、语文成绩、数学成绩、英语成绩呢?
那么还是用分组的思路,首先根据姓名将数据分组成以学生为单位的数据集,然后在这个数据集中筛选出各科成绩,代码如下:
select temptable.name,a.price as '语文',b.price as '数学',c.price as '英语'
from temptable,
(select name,price from temptable where couname = '语文' group by name) as a,
(select name,price from temptable where couname = '数学' group by name) as b,
(select name,price from temptable where couname = '英语' group by name) as c
where temptable.name = a.name
and temptable.name = b.name
and temptable.name = c.name
group by temptable.name;
或者:
select temptable.name,
a.price as '语文',
b.price as '数学',
c.price as '英语'
from temptable,
(select * from temptable where couname = '语文')as a,
(select * from temptable where couname = '数学')as b,
(select * from temptable where couname = '英语')as c
where temptable.name = a.name
and temptable.name = b.name
and temptable.name = c.name
group by temptable.name;
结果如下:
+------+-----+------+------+
| name | 语文 | 数学 | 英语 |
+------+-----+------+------+
| 小明 | 83 | 70 | 80 |
| 小红 | 70 | 80 | 82 |
| 小黑 | 60 | 75 | 82 |
+------+-----+------+------+
因为这必须要对同一个学生下的成绩做二次利用,所以个人认为势必需要先得到临时表再重新组合到一起,但是又目前还很难做优化,或许有更加简单的手段,或许不需要临时表,希望自己在将来的工作学习中可以再有新收获,也希望网上的朋友如果看到可以告诉我最优的处理办法,一起讨论一起提高!