3表联合查询，按产地排序，并要求每个产地中有一个最低价格的sql语句

三表联合查询，按产地排序，并要求每个产地中有一个最低价格的sql语句
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
  WHERE tc.Id in(
  SELECT Id FROM T_Cotton WHERE 
CottonQuote in (SELECT MIN(CottonQuote) FROM dbo.T_Cotton where UserId =1 AND tc.status<>0 group BY CottonArea))

------解决方案--------------------

探讨

引用:

试试，真心看着眼晕
SQL code

WITH A AS
(SELECT TOP 5 tc.Id,
tc.CottonLength,
tc.CottonMavalue,
tc.CottonStrength,
tc.CottonQuote,
tc.CottonType,
tc.CottonSailing,
CASE tc.CottonTyp………