SqlServe_routapply 使用

<pre><code>
SELECT

newstate=case when (isnull(accfirst,0)>0 AND isnull(accarrived,0)>0 AND isnull(accback,0)>0 ) then (isnull(accfirstok,0)+(isnull(accarrivedok,0)+(SELECT sum(hx_status) FROM B_VEHICLE_account WHERE inoneflag=a.inonevehicleflag AND hx_status=1))+(case when (isnull(accbackok,0)=1 AND isnull(accleft,0)=0) then 1 else 0 end )) -- 现付，到付，回付
              when (isnull(accfirst,0)>0 AND isnull(accarrived,0)>0 AND isnull(accback,0)=0 AND isnull(accfirstok,0)=1 AND ( isnull(accarrivedok,0)=1 or  ((SELECT sum(hx_status) FROM B_VEHICLE_account WHERE inoneflag=a.inonevehicleflag AND hx_status=1)=(SELECT count(1) FROM B_VEHICLE_account WHERE inoneflag=a.inonevehicleflag AND acc>0)) )) then 3  --现付，到付
              when (isnull(accfirst,0)>0 AND isnull(accarrived,0)=0 AND isnull(accback,0)>0 AND isnull(accfirstok,0)=1 AND (isnull(accbackok,0)=1 AND isnull(accleft,0)=0)) then 3-- 现付，回付
              when (isnull(accfirst,0)>0 AND isnull(accarrived,0)=0 AND isnull(accback,0)=0 AND isnull(accfirstok,0)=1 ) then 3 --只有现付
              when (isnull(accfirst,0)=0 AND isnull(accarrived,0)>0 AND isnull(accback,0)=0 AND (isnull(accarrivedok,0)=1 or ((SELECT sum(hx_status) FROM B_VEHICLE_account WHERE inoneflag=a.inonevehicleflag AND hx_status=1)=(SELECT count(1) FROM B_VEHICLE_account WHERE inoneflag=a.inonevehicleflag AND acc>0)) )) then 3 --只有到付
              when ( isnull(accfirst,0)=0 AND isnull(accarrived,0)=0 AND isnull(accback,0)>0 AND (isnull(accbackok,0)=1 AND isnull(accleft,0)=0) ) then 3 --只有回付
              when ( isnull(accfirst,0)=0 AND isnull(accarrived,0)>0 AND isnull(accback,0)>0 AND (isnull(accarrivedok,0)=1 or ((SELECT sum(hx_status) FROM B_VEHICLE_account WHERE inoneflag=a.inonevehicleflag AND hx_status=1)=(SELECT count(1) FROM B_VEHICLE_account WHERE inoneflag=a.inonevehicleflag AND acc>0))  ) AND (isnull(accbackok,0)=1 AND isnull(accleft,0)=0)) then 3 --只有到付回付
              else 0 end,billdate as senddate

FROM B_VEHICLE_HT as a left join b_vehicle_trace as b on a.inonevehicleflag=b.inoneflag
outer apply (select
weighttotal=sum(d.weight * c.qty / d.qty),
volumntotal=sum(d.volumn * c.qty / d.qty),
acctrans=sum(d.acctrans * c.qty / d.qty),
acctotal_in=sum((d.accnow + d.accarrived + d.accback + d.accmonth + d.acchuokuankou)* c.qty / d.qty),
acchuikou=sum(d.acchuikou * c.qty / d.qty),
bdqita=sum(d.bdqita * c.qty / d.qty),
accfact=sum((d.accnow + d.accarrived + d.accback + d.accmonth + d.acchuokuankou - d.acchuikou - d.bdqita) * c.qty / d.qty),
qtytotal=sum(c.qty)
from b_fcd as c join b_tyd as d on c.unit=d.unit
where c.inonevehicleflag=a.inonevehicleflag
) as e
WHERE a.billdate BETWEEN @t1 AND @t2
AND a.bsite like @bsite
AND a.esite like @esite

</pre></code>