sql书写:根据年度、专业查询年度、专业、区域营业额(北京营业额、外埠营业额)
select sum(t.bj) as yxebj, sum(t.wb) as nyxmny, nd, zy
from (select substr(sb.dreportdate, 0, 4) as nd,
bd.name as zy,
(case
when sb.yxesbqy = '1001A8100000000LBG4Y'
then b.nyxmny
else 0
end) as bj,
(case
when sb.yxesbqy = '1001A8100000000LBG4Y'
then b.nyxmny
else 0
end) as wb
from cj_yxesb_b b
left join cj_yxesb sb
on b.pk_marketingreport = sb.pk_marketingreport
left join bd_defdoc bc
on bc.pk_defdoclist = sb.yxesbqy
left join bd_defdoc bd
on bd.pk_defdoc = b.pk_projectpr
where nvl(sb.dr, 0) = 0
and nvl(b.dr, 0) = 0
and nvl(bc.dr, 0) = 0
and bd.name like '%岩土%'
and substr(sb.dbilldate, 0, 4) = '2022'
and nvl(bd.dr, 0) = 0 )t
group by t.nd, t.zy;