WITH A AS
(SELECT TOP 5 tc.Id,  
  tc.CottonLength,  
  tc.CottonMavalue,  
  tc.CottonStrength,  
  tc.CottonQuote,  
  tc.CottonType,
  tc.CottonSailing,  
  CASE tc.CottonType WHEN '1' THEN '港口寄售' WHEN '0' THEN '远期交易' ELSE '' END AS TypeName,
  tc.CottonCount,  
  tc.CottonRemark,
  tsc2.[SysName] AS CottonArea,
  tsc3.[SysName] AS CottonClass,
  tsc4.[SysName] AS CottonYear,
  tsc5.[SysName] AS CottonWarehouse
  FROM dbo.T_Cotton tc
  LEFT JOIN dbo.T_SysCode tsc2 ON tc.CottonArea =tsc2.Id
  LEFT JOIN dbo.T_SysCode tsc3 ON tc.CottonClass=tsc3.Id
  LEFT JOIN dbo.T_SysCode tsc4 ON tc.CottonYear = tsc4.Id
  LEFT JOIN dbo.T_SysCode tsc5 ON tc.CottonWarehouse = tsc5.Id
  LEFT JOIN dbo.T_Sale ts ON ts.CottonId=tc.Id
  )
  
  SELECT * 
  FROM A AS T
  WHERE (select count(*) from A where CottonArea =t.CottonArea  and CottonQuote>t.CottonQuote )<1