订单完成情况检查

DECLARE @spid CHAR(11)
SET @spid ='SPH00000068'
SELECT a.djbh,a.dj_sn,a.spid,a.shl,a.yiwchsl,a.is_zx,isnull(b.shl,0) AS ywcsl
FROM jxddmx AS a 
     LEFT JOIN (
SELECT m.xgdjbh,m.recnum,sum(isnull(m.shl,0)) AS shl
FROM(
SELECT xgdjbh,recnum,sum(shl) AS shl FROM jzorder_mx WHERE djbh LIKE 'JHG%' AND spid =@spid AND is_zx<>'清' GROUP BY xgdjbh,recnum
UNION all
SELECT xgdjbh,recnum,sum(shl) AS shl FROM jzorder_mx_his WHERE djbh LIKE 'JHG%' AND spid =@spid AND is_zx<>'清' GROUP BY xgdjbh,recnum
    ) AS m
    GROUP BY m.xgdjbh,m.recnum
     ) AS b ON a.djbh = b.xgdjbh AND a.dj_sn = b.recnum
    
WHERE a.spid=@spid 
      --AND a.shl - isnull(b.shl,0) > 0
--UPDATE jxddmx SET is_zx='是',yiwchsl=288 WHERE djbh='JHFGYT00001407' AND dj_sn =7