一段不太难写的SQL

SELECT a.SONumber, a.CurrencyCode
FROM (
 SELECT SoNumber, CurrencyCode = MIN(CurrencyCode)
 FROM newegg.dbo.SO_COMReasonCode_Log WITH(NOLOCK) 
 WHERE ReasonCode IS NOT NULL AND  SOStatus = 'C' 
 GROUP BY SONumber
 HAVING COUNT(SONumber) = SUM(CASE WHEN ReasonCode IS NOT NULL THEN 1 ELSE 0 END)
 ) a
WHERE NOT EXISTS (SELECT TOP 1 1  
      FROM newegg.dbo.SO_COMAllocation_LogMaster WITH (NOLOCK) 
      WHERE SoNumber = a.SONumber)

               

 

或则

==================================================

SELECT a.SONumber, a.CurrencyCode

    FROM (

     SELECT SoNumber, CurrencyCode = MIN(CurrencyCode)

     FROM dbo.SO_COMReasonCode_Log WITH(NOLOCK) 

     WHERE SOStatus = 'C' 

     GROUP BY SONumber

     -- make sure reasonCode of SO are all NOT NULL

     HAVING SUM(CASE WHEN ReasonCode IS NULL THEN 1 ELSE 0 END) = 0

     ) a

    WHERE NOT EXISTS (SELECT TOP 1 1  

          FROM dbo.SO_COMAllocation_LogMaster b WITH (NOLOCK) 

          WHERE b.SoNumber = a.SONumber)