SQL Server-需要基于嵌套秩返回数据

您想加入嵌套查询。我对适当的连接条件做了一个假设,但它是这样的:

SELECT Name, Address, City, State, Zip 
FROM CUSTOMERS C
INNER JOIN (SELECT TOP 100 CustomerID, TerritoryName, 
            Rank() OVER PARTITION BY TID ORDER BY TerritoryName DESC) AS 'RANK'
            FROM Territories
            WHERE Nation = 'Canada') T
ON C.Customer_Location = T.TerritoryName

我也不确定连接条件。这似乎出乎意料 Customer_Location 和 CustomerID

我想您需要一个CTE或派生表。

WITH R AS
(
SELECT CustomerID, 
Rank() OVER (PARTITION BY TID ORDER BY TerritoryName DESC) AS [rank]
FROM Territories
WHERE Nation = 'Canada'
)


SELECT Name, Address, City, State, Zip 
  FROM CUSTOMERS
 WHERE Customer_Location IN (SELECT CustomerID FROM R WHERE [rank] <= 100)