SQL练习

1. MySQL数据库中有一个学生成绩表student，一共有name、score两个字段，分别表示学生的姓名和分数。请查询出分数排名前60%的学生名单及其对应的分数。

select name,score from student limit 0,(select count(*) * 0.6 from student) order by score desc;

year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

方法一：

SELECT 
      year, 
      MAX(CASE month WHEN '1' THEN amount ELSE 0 END) AS m1,
      MAX(CASE month WHEN '2' THEN amount ELSE 0 END) AS m2,
      MAX(CASE month WHEN '3' THEN amount ELSE 0 END) AS m3,
      MAX(CASE month WHEN '4' THEN amount ELSE 0 END) AS m4
FROM a
GROUP BY year

方法二：

select year,

(select amount from aaa m where month=1 and m.year=aaa.year) as m1,

(select amount from aaa m where month=2 and m.year=aaa.year) as m2,

(select amount from aaa m where month=3 and m.year=aaa.year) as m3,

(select amount from aaa m where month=4 and m.year=aaa.year) as m4

from aaa group by year