SELECT
*
FROM (
SELECT
case when so_d.iQuantity = so_d.iFHQuantity and so_d.cSCloser is null then '不正常'
when so_d.iQuantity = so_d.iFHQuantity and so_d.cSCloser ='asuser' then '正常'
when so_d.cSCloser != 'asuser' and so_d.cSCloser is not null then '取消'
end status,
case when COALESCE(so_d.foutquantity,0) = COALESCE(rd32_summary.iQuantity, 0) then '正常'
when COALESCE(so_d.foutquantity,0) != COALESCE(rd32_summary.iQuantity, 0) then '不正常'
end status_2,
so_m.cSOCode,
so_m.dDate,
so_m.dverifysystime,
so_d.cSCloser,
so_d.AutoID,
so_d.iSOsID,
so_d.cInvCode,
so_d.cFree1,
so_d.iQuantity,
so_d.iFHQuantity,
so_d.foutquantity,
so_d.foutnum,
rd32_summary.latest_date latest_date,
rd32_summary.latest_veri_time latest_veri_date,
rd32_summary.c c,
rd32_summary.iQuantity sum_iQuantity,
rd32_summary.iNum sum_iNum,
so_d.iKPQuantity,
so_d.fVeriDispQty,
row_number() over(partition by case when so_d.iQuantity = so_d.iFHQuantity and so_d.cSCloser is null then '不正常'
when so_d.iQuantity = so_d.iFHQuantity and so_d.cSCloser ='asuser' then '正常'
end ORDER BY iSOsID desc) r
--
-- update so_d
-- SET foutquantity = rd32_summary.iQuantity,
-- foutnum = rd32_summary.iNum

FROM [dbo].[SO_SODetails] so_d

LEFT JOIN SO_SOMain so_m
ON so_d.ID = so_m.ID

LEFT JOIN (
SELECT
COALESCE(iorderdid, isodid) isodid,
max(rd32_m.dDate) latest_date,
max(rd32_m.dnverifytime) latest_veri_time,
count(*) c,
sum(iQuantity) iQuantity,
sum(iNum) iNum
FROM rdrecords32 rd32_d

LEFT JOIN rdrecord32 rd32_m
ON rd32_m.ID = rd32_d.ID

GROUP BY  COALESCE(iorderdid, isodid)
) rd32_summary
ON rd32_summary.isodid = so_d.iSOsID

WHERE 1=1
-- AND rd32_summary.latest_date < '2023-03-06'
AND so_d.iFHQuantity > 0
-- AND so_d.iQuantity = so_d.iFHQuantity
AND so_d.cSCloser is null
AND COALESCE(so_d.foutquantity,0) != COALESCE(rd32_summary.iQuantity, 0)
) tmp
where 1=1
-- 查看不正常数据
-- AND (
-- status='不正常'
-- OR status_2='不正常'
-- )
AND status_2='不正常'
-- AND tmp.r < 10

ORDER BY tmp.latest_veri_date