今天写了个复杂的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;