SQL 面试题 数据表转置

SQL 面试题

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

有一个这样的数据表t_result,要求转换为这种形式

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  t_result m where month=1  and m.year=t_result.year) as m1,
(select amount from  t_result m where month=2  and m.year=t_result.year) as m2,
(select amount from  t_result m where month=3  and m.year=t_result.year) as m3,
(select amount from  t_result m where month=4  and m.year=t_result.year) as m4
from t_result  group by year

如果是oracle还可以用partition over

select * from (
select year, amount as m1, lead(amount) over
(partition by year order by month) m2, lead(amount,2) over(partition by year order by month) m3,
lead(amount,3) over(partition by year order by month) m4, rank() over (partition by year order by month) rk
from t_result) where rk=1

partition over

select year, amount as m1, lead(amount) over
(partition by year order by month) m2, lead(amount,2) over(partition by year order by month) m3,
lead(amount,3) over(partition by year order by month) m4, rank() over (partition by year order by month) rk
from t_result

的结果是

YEAR M1 M2 M3 M4 RK
1991 1.1 1.2 1.3 1.4 1
1991 1.2 1.3 1.4 2
1991 1.3 1.4 3
1991 1.4 4
1992 2.1 2.2 2.3 2.4 1
1992 2.2 2.3 2.4 2
1992 2.3 2.4 3
1992 2.4 4

lead(列名,n,m): 当前记录后面第n行记录的<列名>的值,没有则默认值为m;如果不带参数n,m,则查找当前记录后面第一行的记录<列名>的值,没有则默认值为null。

参考
http://blog.csdn.net/fu0208/article/details/7179001