SQL 无法绑定多部分标识符 - SubQuery
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14656582/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The multi-part identifier could not be bound - SubQuery
提问by Matthew Azkimov
Schema:
架构：
create table TableA (A1 int)
create table TableB (B1 int, B2 int)
create table TableC (C1 int)
Problematic query:
有问题的查询：
SELECT *
FROM TableA a
INNER JOIN TableB b ON b.B1=a.A1
INNER JOIN (SELECT TOP 1 *
FROM TableC c
WHERE c.C1=b.B1 ORDER BY c.C1) d ON d.C2=b.B2
INNER JOIN OtherTable ON OtherTable.Foo=d.C1
Building this schema and running the query in SQLFiddle under SQL Server 2008 results in:
构建此架构并在 SQL Server 2008 下的 SQLFiddle 中运行查询会导致：
The multi-part identifier "b.B1" could not be bound.: SELECT * FROM TableA a INNER JOIN TableB b ON b.B1=a.A1 INNER JOIN (SELECT TOP 1 * FROM TableC c WHERE c.C1=b.B1 ORDER BY c.C1) d ON d.C2=b.B2
Using CROSS APPLY instead of INNER JOIN for the subquery fixes the issue
对子查询使用 CROSS APPLY 而不是 INNER JOIN 解决了这个问题
What's the problem?
有什么问题？
Edit: I added "TOP 1" that was part of the real query and it's a relevant part of the problem.
编辑：我添加了“TOP 1”，它是真实查询的一部分，它是问题的相关部分。
Edit2: Further information about the problem.
Edit2：有关该问题的更多信息。
采纳答案by Hamlet Hakobyan
you can't reference from JOINclause to another part of JOIN.
您不能将 fromJOIN子句引用到 JOIN 的另一部分。
Use this instead.
改用这个。
SELECT *
FROM TableA a
INNER JOIN TableB b
ON b.B1=a.A1
INNER JOIN TableC c
ON d.C2=b.B2
AND c.C1=b.B1
EDITED
已编辑
SELECT *
FROM TableA a
INNER JOIN TableB b ON b.B1=a.A1
WHERE b.B2 = (SELECT TOP 1 c.C2
FROM TableC c
WHERE c.C1=b.B1 ORDER BY c.C1)
For further use of TableCin JOIN-s you can use this.
为了进一步TableC在 JOIN-s 中使用，您可以使用它。
SELECT *
FROM TableA a
INNER JOIN TableB b
ON b.B1=a.A1
INNER JOIN
(
SELECT
ROW_NUMBER() OVER (PARTITION BY C1 ORDER BY C2) RN,
C2
--, other columns fromTableC if needed
FROM TableC
) CC
ON b.B2 = CC.C2
AND CC.RN = 1
回答by Taryn
You cannot access an alias from a join inside of another joined subquery. You will need to use the following which joins the subquery on two columns/tables:
您不能从另一个连接的子查询内的连接访问别名。您将需要使用以下将子查询连接到两个列/表的方法：
SELECT *
FROM TableA a
INNER JOIN TableB b
ON b.B1=a.A1
INNER JOIN
(
SELECT *
FROM TableC c
) d
ON d.C2=b.B2
AND d.C1 = b.B1
Or this can be written as:
或者这可以写成：
SELECT *
FROM TableA a
INNER JOIN TableB b
ON b.B1=a.A1
INNER JOIN TableC c
ON c.C2=b.B2
AND c.C1 = b.B1
