日期:2014-05-17  浏览次数:20737 次

求一条分类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
------解决方案