SQL解惑二：谜题41的三种解法

构建数据语句：

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/