求一条分类SQL
有表 TABLE
记录如下：
name value pn
=============================
A 10000 100
A 8000 90
A 7000 80
..........................
B 100 100
B 90 90
B 80 80
..........................
C 95 100
...........................
C 15 0
其中pn均为的值均为从0到100，每隔10，共11条记录
现经过SQL后的列表如下：
name 100 90 80 70 60 50 40 30 20 10 0
==========================================================
A 10000 8000 7000 .....................................
B 100 90 80 ........................................
C 90 ........................................ 15
..............................................
SQL该如何写???
[解决办法]
create table test( name varchar2(10),value int,pn int);
/
insert into test
select 'A ',10000,100 from dual
union all
select 'A ',8000,90 from dual
union all
select 'A ',7000,80 from dual
union all
select 'B ',100,100 from dual
union all
select 'B ',90,90 from dual
union all
select 'B ',80,80 from dual
union all
select 'C ',95,100 from dual
union all
select 'C ',15,0 from dual;
/
select
name,
sum(decode(pn,100,value,0)) "100 ",
sum(decode(pn,90,value,0)) "90 ",
sum(decode(pn,80,value,0)) "80 ",
sum(decode(pn,70,value,0)) "70 ",
sum(decode(pn,60,value,0)) "60 ",
sum(decode(pn,50,value,0)) "50 ",
sum(decode(pn,40,value,0)) "40 ",
sum(decode(pn,30,value,0)) "30 ",
sum(decode(pn,20,value,0)) "20 ",
sum(decode(pn,10,value,0)) "10 ",
sum(decode(pn,0,value,0)) "0 "
from test
group by name,pn
order by name,pn desc
/
--Result
1A100000000000000
2A08000000000000
3A00700000000000
4B1000000000000
5B090000000000
6B008000000000
7C950000000000
8C000000000015
[解决办法]
不用0显示你想用什么显示
select
name,
sum(decode(pn,100,value,0)) "100 ",
sum(decode(pn,90,value,0)) "90 ",
sum(decode(pn,80,value,0)) "80 ",
sum(decode(pn,70,value,0)) "70 ",
sum(decode(pn,60,value,0)) "60 ",
sum(decode(pn,50,value,0)) "50 ",
sum(decode(pn,40,value,0)) "40 ",
sum(decode(pn,30,value,0)) "30 ",
sum(decode(pn,20,value,0)) "20 ",
sum(decode(pn,10,value,0)) "10 ",
sum(decode(pn,0,value,0)) "0 "
from test
group by name
order by name asc
--Result
1A100008000700000000000
2B100908000000000
3C9500000000015
[解决办法]
把楼上的这些
sum(decode(pn,90,value,0)) "90 "
换成
case when pn = '90 ' then sum(value) end "90 "
可以解决：SELECT出来的值全部为0
