问题简化了一下,有两个表
order_test供货计划表
————————————————
order_con_no 供货计划编号
department 管理局
time 时间YYYYMMDD
type 物资类型
order_quan 供货计划量disp_test调拨表
————————————————
disp_con_no 调拨编号
department 管理局
time 时间YYYYMMDD
type 物资类型
disp_quan 调拨量注:同样的管理局、时间、物资类型可能对应多个供货计划编号和调拨编号想做的是编函数或存储过程,传入参数是管理局和物资类型,要以游标的形式返回这个管理局、这个物资类型,今年每个月的供货计划总量、调拨总量、调拨总量/供货计划总量。(也就是按月把调拨量相加,把供货计划量相加,两者在相除)返回是n*2的二维表,不知道有没有办法写一个select语句也可以分开写可以,合在一起就不会了,还要做除法??????有没有什么办法???
select sum(order_quan) from order_test
where department='GD'
and type='11'
and substr(time,1,4)='2008'
group by substr(time,1,6)
order by substr(time,1,6)select sum(disp_quan) from disp_test
where department='GD'
and type='11'
and substr(time,1,4)='2008'
group by substr(time,1,6)
order by substr(time,1,6)
order_test供货计划表
————————————————
order_con_no 供货计划编号
department 管理局
time 时间YYYYMMDD
type 物资类型
order_quan 供货计划量disp_test调拨表
————————————————
disp_con_no 调拨编号
department 管理局
time 时间YYYYMMDD
type 物资类型
disp_quan 调拨量注:同样的管理局、时间、物资类型可能对应多个供货计划编号和调拨编号想做的是编函数或存储过程,传入参数是管理局和物资类型,要以游标的形式返回这个管理局、这个物资类型,今年每个月的供货计划总量、调拨总量、调拨总量/供货计划总量。(也就是按月把调拨量相加,把供货计划量相加,两者在相除)返回是n*2的二维表,不知道有没有办法写一个select语句也可以分开写可以,合在一起就不会了,还要做除法??????有没有什么办法???
select sum(order_quan) from order_test
where department='GD'
and type='11'
and substr(time,1,4)='2008'
group by substr(time,1,6)
order by substr(time,1,6)select sum(disp_quan) from disp_test
where department='GD'
and type='11'
and substr(time,1,4)='2008'
group by substr(time,1,6)
order by substr(time,1,6)
from order_test
where department = 'GD'
and type = '11'
and substr(time, 1, 4) = '2008'
group by substr(time, 1, 6)
order by substr(time, 1, 6)
where ordert.department=dispt.department and ordert.type=dispt.type and (substr(ordert.time,1,4)=substr(dispt.time,1,4))
group by ordert.department试试这个
decode(time,'200801',decode(type,2,quan)) disp_quan1
from
(select sum(order_quan) quan,substr(time,1,6) time ,1 type
from order_test
where department='GD'
and type='11'
and substr(time,1,4)='2008'
group by substr(time,1,6)
union all
select sum(disp_quan),substr(time,1,6) ,2
from disp_test
where department='GD'
and type='11'
and substr(time,1,4)='2008'
group by substr(time,1,6)
)
这个各种情况都测试过了!是可以的!
select A.department,A.b1,A.type,a.a1,b.a2,b.a2/a.a1 from
(select department,substr(time,1,6) b1,substr(time,1,4) c1,type,sum(order_quan) a1 from order_test group by department,substr(time,1,6),type,substr(time,1,4)) A full join
(select department,substr(time,1,6) b2,substr(time,1,4) c2,type,sum(disp_quan) a2 from disp_test group by department,substr(time,1,6),type,substr(time,1,4)) B
on A.department=B.department and A.type=B.type and A.b1=B.b2
where (B.type='11' or A.type='11') and (A.department='GD' or B.department='GD') and (A.c1='2008' or B.c2='2008')
order by A.b1,B.b2
select nvl(A.b1,B.b2),A.a1,B.a2,B.a2/A.a1 from
(select department,substr(time,1,6) b1,substr(time,1,4) c1,type,sum(order_quan) a1 from order_test group by department,substr(time,1,6),type,substr(time,1,4)) A full join
(select department,substr(time,1,6) b2,substr(time,1,4) c2,type,sum(disp_quan) a2 from disp_test group by department,substr(time,1,6),type,substr(time,1,4)) B
on A.department=B.department and A.type=B.type and A.b1=B.b2
where (B.type='11' or A.type='11') and (A.department='GD' or B.department='GD') and (A.c1='2008' or B.c2='2008')
order by nvl(A.b1,B.b2)