小弟有一存储过程问题需要解决:
将表结构SERVICE_ID FEE_DATE 开帐应收 市话
13393280001 200804 100 23
13393280001 200805 150 23
13393280001 200806 100 23
13393280002 200804 100 23
13393280002 200806 10 23
13393280003 200804 102 23
13393280003 200805 120 23
13393280003 200806 140 23
13393280004 200806 100 23存储过程查询得到如下结果:SERVICE_ID 开帐应收(200804) 开帐应收(200805) 开帐应收(200806) 市话(200804) 市话(200805) 市话(200806)
13393280001 100 150 100 23 23 23
13393280002 100 0 10 23 23 23
13393280003 102 120 140 23 23 23
13393280004 0 0 100 23 23 23其中的月份即(FEE_DATE字段)时间是不固定的求用简单SQL语句实现最好是一句,不能使用游标循环或临时表。
将表结构SERVICE_ID FEE_DATE 开帐应收 市话
13393280001 200804 100 23
13393280001 200805 150 23
13393280001 200806 100 23
13393280002 200804 100 23
13393280002 200806 10 23
13393280003 200804 102 23
13393280003 200805 120 23
13393280003 200806 140 23
13393280004 200806 100 23存储过程查询得到如下结果:SERVICE_ID 开帐应收(200804) 开帐应收(200805) 开帐应收(200806) 市话(200804) 市话(200805) 市话(200806)
13393280001 100 150 100 23 23 23
13393280002 100 0 10 23 23 23
13393280003 102 120 140 23 23 23
13393280004 0 0 100 23 23 23其中的月份即(FEE_DATE字段)时间是不固定的求用简单SQL语句实现最好是一句,不能使用游标循环或临时表。
http://topic.csdn.net/u/20080416/11/910e40c1-60f1-441f-8b0f-19a969d30f77.html
max(decode(rn,1,column3,null)) aa04,
max(decode(rn,2,column3,null)) aa05,
max(decode(rn,3,column3,null)) aa06,
max(decode(rn,1,column4,null)) bb04,
max(decode(rn,2,column4,null)) bb05,
max(decode(rn,3,column4,null)) bb06
from (select column1,column2,column3,column4,row_number() over(partition by column1 order by rownum) rn
from (select * from (SELECT * FROM table1
union all
select * from(select distinct m.column1,n.column2,'0' as column3,23 as column4
from table1 m,table1 n)
where (column1,column2) not in (select column1,column2 from table1))
order by column1,column2))
group by column1
功能已经实现了,写的比较乱,楼主自己优化一下!
select column1 service_id,
max(decode(column2,'200804',column3,null)) aa04,
max(decode(column2,'200805',column3,null)) aa05,
max(decode(column2,'200806',column3,null)) aa06,
max(decode(column2,'200804',column4,null)) bb04,
max(decode(column2,'200805',column4,null)) bb05,
max(decode(column2,'200806',column4,null)) bb06
from (select * from (SELECT * FROM table1
union all
select * from(select distinct m.column1,n.column2,'0' as column3,23 as column4
from table1 m,table1 n)
where (column1,column2) not in (select column1,column2 from table1))
order by column1,column2)
group by column1