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
[解决办法]
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
