本文共 1904 字，大约阅读时间需要 6 分钟。
SELECT aa.dn_vbeln,
aa.customername,
aa.PERSON_NAME,
aa.CARNUM,
aa.DRIVER,
aa.SHDAT,
aa.MATNR,
aa.MAKTX,
aa.T_COUNT,
aa.I_COUNT,
aa.SY_COUNT,
aa.I_UNIT_NAME,
aa.DN_AMT,
aa.ST_AMT,
aa.SY_AMT,
aa.remark
FROM (SELECT A.VBELN as dn_vbeln,
A.NAME as customername,
A.PERSON_NAME,
D.car_name carnum,
D.DRIVER,
D.SHDAT,
B.MATNR,
B.MAKTX,
B.T_COUNT,
B.I_COUNT,
B.T_COUNT-I_COUNT as SY_COUNT,
B.I_UNIT_NAME,
B.T_COUNT*B.ZH_PRICE as DN_AMT,
B.I_COUNT*B.ZH_PRICE as ST_AMT,
(B.T_COUNT- I_COUNT) *B.ZH_PRICE as SY_AMT,'部分签收'remark
FROM dn_order A
JOIN dn_order_item B
ON A.VBELN=B.VBELN
AND A.DID=B.DID
AND B.T_COUNT!=B.I_COUNT
LEFT JOIN pc_orders C
ON A.VBELN= C.DN_VBELN AND A.DID =C.DID
LEFT JOIN pc_order D ON C.VBELN= D.VBELN AND C.DID =D.DID
WHERE1 = 1AND B.T_COUNT> 0
AND D.SHDAT>=#{dateStart}
AND D.SHDAT<=#{dateEnd}AND A.did=#{did}
and (UPPER (A.VBELN) like #{key} or
UPPER (A.KUNNR) like #{key} or
UPPER (A.NAME) like #{key} or
UPPER (A.PERSON_NAME) like #{key} or
UPPER (D.CARNUM) like #{key} or
UPPER (B.MATNR) like #{key} or
UPPER (B.MAKTX) like #{key}
)UNION ALL
SELECT A.VBELN dn_vbeln,
A.NAME as customername,
A.PERSON_NAME,
D.car_name carnum,
D.DRIVER,
D.SHDAT,
B.MATNR,
B.MAKTX,
B.T_COUNT,0I_COUNT,
I_COUNT SY_COUNT,
B.I_UNIT_NAME,
B.T_COUNT*B.ZH_PRICE DN_AMT,0ST_AMT,
I_COUNT*B.ZH_PRICE SY_AMT,'全部未收'REMARK
FROM dn_order A
JOIN dn_order_item B
ON A.VBELN=B.VBELN
AND A.DID=B.DID
AND B.T_COUNT=B.I_COUNT
AND A.STATUS= '20'LEFT JOIN pc_orders C
ON A.VBELN= C.DN_VBELN AND A.DID =C.DID
LEFT JOIN pc_order D ON C.VBELN= D.VBELN AND C.DID =D.DID
WHERE1 = 1AND B.T_COUNT> 0
AND D.SHDAT>=#{dateStart}
AND D.SHDAT<=#{dateEnd}AND A.did=#{did}
and (UPPER (A.VBELN) like #{key} or
UPPER (A.KUNNR) like #{key} or
UPPER (A.NAME) like #{key} or
UPPER (A.PERSON_NAME) like #{key} or
UPPER (D.CARNUM) like #{key} or
UPPER (B.MATNR) like #{key} or
UPPER (B.MAKTX) like #{key}
)) aa
ORDER BY aa.REMARK, aa.SHDAT DESC
转载地址：http://nrffk.baihongyu.com/