SQL子查询过滤遇多部分标识符绑定错误，求获取各供应点最新读数

你之前的查询报错The multi-part identifier "R.SupplyPointInstallationId" could not be bound，原因是子查询无法访问外层查询的R.SupplyPointInstallationId——你用了旧式的逗号连接表的方式，这个子查询属于非关联子查询，不能引用外层表的字段。

以下是几种可靠的修改方案，都能实现“每个指定SupplyPointInstallationId对应最新一条Reading记录”的需求：

方案一：使用ROW_NUMBER()窗口函数（推荐） #

通过窗口函数给每个SupplyPointInstallationId分组内的记录按LastDate倒序编号，取编号为1的记录就是每组最新的：

DECLARE @supplyPointIds TABLE
(
    id UNIQUEIDENTIFIER
)
INSERT INTO @supplyPointIds (id)
VALUES ('1DC9A405-4EC5-4379-BB2C-110973A9936B'),
       ('65745684-7D00-4D8A-9735-2C5BA29852B0')

SELECT LastReadingId, SupplyPointInstallationId
FROM (
    SELECT 
        R.Id AS LastReadingId, 
        R.SupplyPointInstallationId,
        -- 按分组字段分区，每组内按日期倒序排号
        ROW_NUMBER() OVER (PARTITION BY R.SupplyPointInstallationId ORDER BY R.LastDate DESC) AS RowNum
    FROM Reading R
    WHERE R.SupplyPointInstallationId IN (SELECT id FROM @supplyPointIds)
) AS RankedReadings
WHERE RowNum = 1 -- 仅保留每组的第一条（最新记录）
ORDER BY SupplyPointInstallationId DESC

方案二：使用TOP 1 WITH TIES（代码最简洁） #

利用TOP 1 WITH TIES结合窗口函数，直接返回所有分组的最新记录：

DECLARE @supplyPointIds TABLE
(
    id UNIQUEIDENTIFIER
)
INSERT INTO @supplyPointIds (id)
VALUES ('1DC9A405-4EC5-4379-BB2C-110973A9936B'),
       ('65745684-7D00-4D8A-9735-2C5BA29852B0')

SELECT TOP 1 WITH TIES
    R.Id AS LastReadingId, 
    R.SupplyPointInstallationId
FROM Reading R
WHERE R.SupplyPointInstallationId IN (SELECT id FROM @supplyPointIds)
ORDER BY ROW_NUMBER() OVER (PARTITION BY R.SupplyPointInstallationId ORDER BY R.LastDate DESC)

方案三：关联子查询匹配最新日期 #

先查询每个SupplyPointInstallationId对应的最大LastDate，再关联回原表获取对应记录：

DECLARE @supplyPointIds TABLE
(
    id UNIQUEIDENTIFIER
)
INSERT INTO @supplyPointIds (id)
VALUES ('1DC9A405-4EC5-4379-BB2C-110973A9936B'),
       ('65745684-7D00-4D8A-9735-2C5BA29852B0')

SELECT 
    R.Id AS LastReadingId, 
    R.SupplyPointInstallationId
FROM Reading R
INNER JOIN (
    SELECT 
        SupplyPointInstallationId, 
        MAX(LastDate) AS MaxLastDate
    FROM Reading
    WHERE SupplyPointInstallationId IN (SELECT id FROM @supplyPointIds)
    GROUP BY SupplyPointInstallationId
) AS LatestDates 
    ON R.SupplyPointInstallationId = LatestDates.SupplyPointInstallationId 
    AND R.LastDate = LatestDates.MaxLastDate
ORDER BY R.SupplyPointInstallationId DESC

内容的提问来源于stack exchange，提问作者amedina