无法绑定多部分标识符 - 子查询 [英] The multi-part identifier could not be bound - SubQuery
本文介绍了无法绑定多部分标识符 - 子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
架构:
create table TableA (A1 int)
create table TableB (B1 int, B2 int)
create table TableC (C1 int)
有问题的查询:
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
构建此架构并在 SQL Server 2008 下的 SQLFiddle 中运行查询会导致:
Building this schema and running the query in SQLFiddle under SQL Server 2008 results in:
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
对子查询使用 CROSS APPLY 而不是 INNER JOIN 可解决问题
有什么问题?
我添加了TOP 1",它是实际查询的一部分,它是问题的相关部分.
I added "TOP 1" that was part of the real query and it's a relevant part of the problem.
Edit2:有关问题的更多信息.
Further information about the problem.
推荐答案
你不能从 JOIN
子句引用到 JOIN 的另一部分.
you can't reference from JOIN
clause to another part of JOIN.
改用这个.
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
已编辑
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)
为了在 JOIN-s 中进一步使用 TableC
,你可以使用这个.
For further use of TableC
in JOIN-s you can use this.
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
这篇关于无法绑定多部分标识符 - 子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文