2018-12-24 sql横竖转化

5.面试题：怎么把这样一个表儿

t1;

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

查成这样一个结

t2;

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, 

(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

二

select year,  sum(case month when '1' then amount else 0 end) as m1

,  sum(case month when '2' then amount else 0 end) as m2

,  sum(case month when '3' then amount else 0 end) as m3

,  sum(case month when '4' then amount else 0 end) as m4

from t1 ground by year 

t2转t1

select year,'1' as month, m1 as mount from t2 

union all 

select year,'2' as month, m2 as mount from t2

union all 

select year,'3' as month, m3 as mount from t2

union all 

select year,'4' as month, m4 as mount from t2

order by year ,month  desc 

二

select year ,cash m1 when 

有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：  

   大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。  

       显示格式：  

       语文              数学                英语  

       及格              优秀                不及格    

select (case when yw>80 then  '优秀'   when yw<60 then '不及格'  else '及格')as 语文，

（case when sx>80 then  '优秀'   when sx<60 then '不及格'  else '及格'）as 数学，

 （case when yy>80 then  '优秀'   when yy<60 then '不及格'  else '及格' ） as 英语

from table