oracle sql 问题
1.这是 user_name 表
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
写一个sql (要求 用 lead over 这些 分析 函数 为了 提高 效率)
实现 下面的 效果
year m1??m2??m3??m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
oracle sql
[解决办法]
with temp as
(
select 1991 year,1 month,1.1 amount from dual
union all
select 1991 year,2 month,1.2 amount from dual
union all
select 1991 year,3 month,1.3 amount from dual
union all
select 1991 year,4 month,1.4 amount from dual
union all
select 1992 year,1 month,2.1 amount from dual
union all
select 1992 year,2 month,2.2 amount from dual
union all
select 1992 year,3 month,2.3 amount from dual
union all
select 1992 year,4 month,2.4 amount from dual
)
select * from temp
PIVOT (SUM(amount) FOR month IN (1 as m1,2 as m2,3 as m3,4 as m4) )
