SQL多部分标识符错误，尝试使用子查询中的字段进行筛选。

huangapple
go评论97阅读模式
英文:

SQL Multipart identifier error when attempting to filter using a field from a subquery

问题

Ok, I'm not very good with SQL, so I don't know how to solve this one. I have this query:

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
WHERE R.SupplyPointInstallationId IN (SELECT id FROM @supplyPointIds)
ORDER BY R.SupplyPointInstallationId, R.LastDate DESC

And it produces this:

SQL多部分标识符错误，尝试使用子查询中的字段进行筛选。

But what I want to get is this only:

SQL多部分标识符错误，尝试使用子查询中的字段进行筛选。

So, I wrote this query:

DECLARE @supplyPointIds TABLE
(
id UNIQUEIDENTIFIER
)
INSERT INTO @supplyPointIds (id)
VALUES ('1DC9A405-4EC5-4379-BB2C-110973A9936B'),
('65745684-7D00-4D8A-9735-2C5BA29852B0')
SELECT ReadingId.Id AS LastReadingId, R.SupplyPointInstallationId
FROM Reading R,
(SELECT TOP 1 RID.Id
FROM Reading RID
WHERE RID.SupplyPointInstallationId = R.SupplyPointInstallationId
ORDER BY RID.LastDate) AS ReadingId -- Only the most recent reading
WHERE R.SupplyPointInstallationId IN (SELECT id FROM @supplyPointIds)
ORDER BY R.SupplyPointInstallationId DESC

But, unfortunately, I get this error:

The multi-part identifier "R.SupplyPointInstallationId" could not be bound.

How can I do to get only those two records I want?

英文:

Ok, I'm not very good with SQL, so I don't know how to solve this one. I have this query:

  1. DECLARE @supplyPointIds TABLE
  2.                         (
  3.                             id UNIQUEIDENTIFIER
  4.                         )
  5. INSERT INTO @supplyPointIds (id)
  6. VALUES ('1DC9A405-4EC5-4379-BB2C-110973A9936B'),
  7.        ('65745684-7D00-4D8A-9735-2C5BA29852B0')
  8. SELECT R.Id AS LastReadingId, R.SupplyPointInstallationId
  9. FROM Reading R
  10. WHERE R.SupplyPointInstallationId IN (SELECT id FROM @supplyPointIds)
  11. ORDER BY R.SupplyPointInstallationId, R.LastDate DESC

And it produces this:

SQL多部分标识符错误，尝试使用子查询中的字段进行筛选。

But what I want to get is this only:

SQL多部分标识符错误，尝试使用子查询中的字段进行筛选。

So, I wrote this query:

  1. DECLARE @supplyPointIds TABLE
  2.                         (
  3.                             id UNIQUEIDENTIFIER
  4.                         )
  5. INSERT INTO @supplyPointIds (id)
  6. VALUES ('1DC9A405-4EC5-4379-BB2C-110973A9936B'),
  7.        ('65745684-7D00-4D8A-9735-2C5BA29852B0')
  8. SELECT ReadingId.Id AS LastReadingId, R.SupplyPointInstallationId
  9. FROM Reading R,
  10.      (SELECT TOP 1 RID.Id
  11.       FROM Reading RID
  12.       WHERE RID.SupplyPointInstallationId = R.SupplyPointInstallationId
  13.       ORDER BY RID.LastDate) AS ReadingId -- Only the most recent reading
  14. WHERE R.SupplyPointInstallationId IN (SELECT id FROM @supplyPointIds)
  15. ORDER BY R.SupplyPointInstallationId DESC

But, unfortunately, I get this error:

> The multi-part identifier "R.SupplyPointInstallationId" could not be
> bound.

How can I do to get only those two records I want?

答案1

得分: 1

以下是您要的代码的中文翻译部分：

  1. 尝试连接这两个表，并使用row_number函数如下：
  3.     选择 R.Id 作为 LastReadingId，R.SupplyPointInstallationId
  4.     从 
  5.       (
  6.         选择 *, 
  7.           ROW_NUMBER() OVER (PARTITION BY SupplyPointInstallationId ORDER BY LastDate DESC) rn
  8.         从 Reading
  9.       ) R 加入 @supplyPointIds S
  10.     在 S.id = R.SupplyPointInstallationId
  11.     其中 R.rn = 1
  13. [查看演示][1]
  15. 这也可以写成：
  17.     选择 LastReadingId，SupplyPointInstallationId
  18.       从
  19.     (
  20.       选择 R.Id 作为 LastReadingId，R.SupplyPointInstallationId，
  21.            ROW_NUMBER() OVER (PARTITION BY R.SupplyPointInstallationId ORDER BY R.LastDate DESC) rn
  22.       从 Reading R 加入 @supplyPointIds S
  23.       在 S.id = R.SupplyPointInstallationId
  24.     ) t
  25.     其中 rn = 1
  27.   [1]: https://dbfiddle.uk/9xghhHhf
英文:

Try to join the two tables and use the row_number function as the following:

  1. SELECT R.Id AS LastReadingId, R.SupplyPointInstallationId
  2. FROM 
  3.   (
  4.     SELECT *, 
  5.       ROW_NUMBER() OVER (PARTITION BY SupplyPointInstallationId ORDER BY LastDate DESC) rn
  6.     FROM Reading
  7.   ) R JOIN @supplyPointIds S
  8. ON S.id = R.SupplyPointInstallationId
  9. WHERE R.rn = 1

See demo

This can be also written as:

  1. SELECT LastReadingId, SupplyPointInstallationId
  2.   From
  3. (
  4.   SELECT R.Id AS LastReadingId, R.SupplyPointInstallationId,
  5.        ROW_NUMBER() OVER (PARTITION BY R.SupplyPointInstallationId ORDER BY R.LastDate DESC) rn
  6.   FROM Reading R JOIN @supplyPointIds S
  7.   ON S.id = R.SupplyPointInstallationId
  8. ) t
  9. WHERE rn = 1

huangapple
  • 本文由 发表于 2023年3月15日 18:55:33
  • 转载请务必保留本文链接：https://go.coder-hub.com/75743749.html
  • sql
  • sql-server
  • t-sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定