求一条分类SQL解决方法
求一条分类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
1 A 10000 0 0 0 0 0 0 0 0 0 0
2 A 0 8000 0 0 0 0 0 0 0 0 0
3 A 0 0 7000 0 0 0 0 0 0 0 0
4 B 100 0 0 0 0 0 0 0 0 0 0
5 B 0 90 0 0 0 0 0 0 0 0 0
6 B 0 0 80 0 0 0 0 0 0 0 0
7 C 95 0 0 0 0 0 0 0 0 0 0
8 C 0 0 0 0 0 0 0 0 0 0 15
------解决方案--------------------
不用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
1 A 10000 8000 7000 0 0 0 0 0 0 0 0
2 B 100 90 80 0 0 0 0 0 0 0 0
3 C 95 0 0 0 0 0 0 0 0 0 15
------解决方案--------------------
把楼上的这些
sum(decode(pn,90,value,0)) "90 "
换成
case when pn = '90 ' then sum(value) end "90 "
可以解决：SELECT出来的值全部为0
有表 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
1 A 10000 0 0 0 0 0 0 0 0 0 0
2 A 0 8000 0 0 0 0 0 0 0 0 0
3 A 0 0 7000 0 0 0 0 0 0 0 0
4 B 100 0 0 0 0 0 0 0 0 0 0
5 B 0 90 0 0 0 0 0 0 0 0 0
6 B 0 0 80 0 0 0 0 0 0 0 0
7 C 95 0 0 0 0 0 0 0 0 0 0
8 C 0 0 0 0 0 0 0 0 0 0 15
------解决方案--------------------
不用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
1 A 10000 8000 7000 0 0 0 0 0 0 0 0
2 B 100 90 80 0 0 0 0 0 0 0 0
3 C 95 0 0 0 0 0 0 0 0 0 15
------解决方案--------------------
把楼上的这些
sum(decode(pn,90,value,0)) "90 "
换成
case when pn = '90 ' then sum(value) end "90 "
可以解决：SELECT出来的值全部为0