SQL查询
数据库中表设计为
TB1
TYPE,LEV,NUM,YEAR
1 , 5 ,100,2010
1 , 6 ,101,2010
2 , 7 ,200,2010
2 , 7 ,201,2011
3 , 5 ,300,2012
3 , 5 ,320,2012
..
要得到这样的数据列表
lev/year 2010 2011 2012
5 100 0 620
6 101 0 0
7 200 201 0
[解决办法]

引用:

数据库中表设计为
TB1
TYPE,LEV,NUM,YEAR
1 , 5 ,100,2010
1 , 6 ,101,2010
2 , 7 ,200,2010
2 , 7 ,201,2011
3 , 5 ,300,2012
3 , 5 ,320,2012
..
要得到这样的数据列表
lev/year 2010 2011 2012
5 100 0 620
6 101 0 0
7 200 201 0

如果YEAR固定，可以用CASE WHEN 或 DECODE 来搞定，如果Year 不固定，一个SQL很难搞定，需要用PROCEDURE
[解决办法]

with table_ta as
(select 1 "TYPE",5 "LEV",100 "NUM",2010 "YEAR" from dual
union all
select  1 , 6 ,101,2010 from dual
union all
select  2 , 7 ,200,2010 from dual
union all
select  2 , 7 ,201,2011 from dual
union all
select   3 , 5 ,300,2012 from dual
union all
select  3 , 5 ,320,2012 from dual
)

--方法一
select a.lev,sum(decode(a.YEAR,2010,a.num,0)) "2010",
sum(decode(a.YEAR,2011,a.num,0)) "2011",
sum(decode(a.YEAR,2012,a.num,0)) "2012" 
from table_ta a group by a.lev order by a.lev

--方法二
select a.lev,sum(case a.YEAR when 2010 then a.num else 0 end) "2010",
sum(case a.YEAR when 2011 then a.num else 0 end) "2011",
sum(case a.YEAR when 2012 then a.num else 0 end) "2012" 
from table_ta a group by a.lev order by a.lev