构建数据语句:
create or replace view 项目(项目编码 , 项目描述 )
as
SELECT 10 , 'Item 10' from dual union all
SELECT 20 , 'Item 20' from dual union all
SELECT 30 , 'Item 30' from dual union all
SELECT 40 , 'Item 40' from dual union all
SELECT 50 , 'item 50' from dual;
create or replace view 实际(项目编码, 实际费用 , 支票号 )
as
SELECT 10 , 300.00 , '1111' from dual union all
SELECT 20 , 325.00 , '2222' from dual union all
SELECT 20 , 100.00 , '3333' from dual union all
SELECT 30 , 525.00 , '1111' from dual;
create or replace view 预算(项目编码 ,贷款 )
as
SELECT 10 , 300.00 from dual union all
SELECT 10 , 50.00 from dual union all
SELECT 20 , 325.00 from dual union all
SELECT 20 , 110.00 from dual union all
SELECT 40 , 25.00 from dual;
要实现的效果:
---41 解答
select a.项目编码, a.项目描述, M.实际费用合计, N.预算合计, M.支票号
from 项目 a
left join (select 项目编码, sum(实际费用) as 实际费用合计, 支票号
from (select s.项目编码,
s.实际费用,
(CASE
WHEN count(DISTINCT s.支票号) OVER(PARTITION BY s.项目编码) > 1 THEN
'Mixed'
ELSE
支票号
END) 支票号
from 实际 s)
group by 项目编码, 支票号 ) M
on (M.项目编码 = a.项目编码)
left join (select y.项目编码, sum(y.贷款) as 预算合计
from 预算 y
group by y.项目编码) N
on (N.项目编码 = a.项目编码)
order by 1;
---with的写法
WITH tmp AS (
SELECT 项目编码,SUM (实际费用 ) 实际费用 ,支票号 FROM (
SELECT 项目编码,实际费用 ,
CASE WHEN count(DISTINCT 支票号) OVER(PARTITION BY 项目编码) > 1 THEN 'Mixed'
ELSE 支票号 END 支票号
FROM 实际 t
) GROUP BY 项目编码, 支票号
)
SELECT 项目.项目编码 ,项目.项目描述 ,sum(实际.实际费用) ACTUAL_TOT,sum( 预算.贷款) ESTIMATE_TOT ,实际.支票号 FROM 项目
LEFT JOIN tmp 实际
ON 项目.项目编码 = 实际.项目编码
LEFT JOIN (SELECT 项目编码,SUM (贷款 ) 贷款 FROM 预算 GROUP BY 项目编码) 预算
ON 项目.项目编码=预算.项目编码
GROUP BY 项目.项目编码 ,项目.项目描述 ,实际.支票号
ORDER BY 项目.项目编码
---row_number() over() 实现
select a.项目编码, a.项目描述, M.实际费用合计, N.预算合计, M.支票号
from 项目 a
left join (
select 项目编码,
sum(实际费用) as 实际费用合计,
max(case when rnt > 1 then '多支票号' else 支票号 end) as 支票号
from (
select s.项目编码,
s.实际费用,
s.支票号,
row_number() over(partition by s.项目编码 order by s.项目编码) as rnt
from 实际 s
)
group by 项目编码
) M
on (M.项目编码 = a.项目编码)
left join (select y.项目编码, sum(y.贷款) as 预算合计
from 预算 y
group by y.项目编码) N
on (N.项目编码 = a.项目编码)
order by 1;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29507357/viewspace-1223211/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29507357/viewspace-1223211/