SQL的连接查询和多条件处理

今天写了个复杂的SQL，多条件的查询

先记下了。

select *
  from (select hsff as HSFF,
               fylb as FYLB,
               sum(SKFY + TKFY) as FY,
               sum(SKBS - TKBS) as BS,
               sum(TKFY) as TKFY,
               sum(TKBS) as TKBS,
               sum(SKFY) as SKFY,
               sum(SKBS) as SKBS
          from (select b.hsff as HSFF,
                       case
                         when b.fylb = 5 and b.hsff = 4 then
                          3
                         else
                          b.fylb
                       end as FYLB,
                       0 as FY,
                       0 as BS,
                       sum(b.fy) as TKFY,
                       sum(b.bs) as TKBS,
                       0 as SKFY,
                       0 as SKBS
                  from mz_sfrj0 a, mz_sfrj1 b
                 where a.rjid = b.rjid
                   and b.xm = 1
                 group by b.hsff, b.fylb
                union all
                select b.hsff as HSFF,
                       case
                         when b.fylb = 5 and b.hsff = 4 then
                          3
                         else
                          b.fylb
                       end as FYLB,
                       0 as FY,
                       0 as BS,
                       0 as TKFY,
                       0 as TKBS,
                       sum(b.fy) as SKFY,
                       sum(b.bs) as SKBS
                  from mz_sfrj0 a, mz_sfrj1 b
                 where a.rjid = b.rjid
                   and b.xm = 0
                 group by b.hsff, b.fylb)
         group by hsff, fylb) d
 where (exists (select 1
                  from zd_mzhs1 e
                 where d.hsff = e.hsff
                   and d.fylb = e.flxh
                   and e.f_sfrj = 1) and d.hsff = 4)
    or d.hsff <> 4
 order by hsff desc, fylb asc;

  

评论

1 楼 flowerjacky 2011-08-06  

请问,能用吗