SQL 无法绑定多部分标识符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7314134/
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
提问by PhamMinh
I've seen similar errors on SO, but I don't find a solution for my problem. I have a SQL query like:
我在 SO 上看到过类似的错误，但我没有找到解决我的问题的方法。我有一个 SQL 查询，如：
SELECT DISTINCT
a.maxa ,
b.mahuyen ,
a.tenxa ,
b.tenhuyen ,
ISNULL(dkcd.tong, 0) AS tongdkcd
FROM phuongxa a ,
quanhuyen b
LEFT OUTER JOIN ( SELECT maxa ,
COUNT(*) AS tong
FROM khaosat
WHERE CONVERT(DATETIME, ngaylap, 103) BETWEEN 'Sep 1 2011'
AND
'Sep 5 2011'
GROUP BY maxa
) AS dkcd ON dkcd.maxa = a.maxa
WHERE a.maxa <> '99'
AND LEFT(a.maxa, 2) = b.mahuyen
ORDER BY maxa;
When I execute this query, the error result is:
The multi-part identifier "a.maxa" could not be bound.Why?
P/s: if i divide the query into 2 individual query, it run ok.
当我执行这个查询时，错误结果是：
无法绑定多部分标识符“a.maxa”。为什么？
P/s：如果我将查询分成 2 个单独的查询，它运行正常。
SELECT DISTINCT
a.maxa ,
b.mahuyen ,
a.tenxa ,
b.tenhuyen
FROM phuongxa a ,
quanhuyen b
WHERE a.maxa <> '99'
AND LEFT(a.maxa, 2) = b.mahuyen
ORDER BY maxa;
and
和
SELECT maxa ,
COUNT(*) AS tong
FROM khaosat
WHERE CONVERT(DATETIME, ngaylap, 103) BETWEEN 'Sep 1 2011'
AND 'Sep 5 2011'
GROUP BY maxa;
回答by Andriy M
You are mixing implicit joins with explicit joins. That is allowed, but you need to be aware of how to do that properly.
您将隐式连接与显式连接混合在一起。这是允许的，但你需要知道如何正确地做到这一点。
The thing is, explicit joins (the ones that are implemented using the JOINkeyword) take precedence over implicit ones (the 'comma' joins, where the join condition is specified in the WHEREclause).
问题是，显式连接（使用JOIN关键字实现的连接）优先于隐式连接（“逗号”连接，在WHERE子句中指定连接条件）。
Here's an outline of your query:
以下是您的查询大纲：
SELECT
…
FROM a, b LEFT JOIN dkcd ON …
WHERE …
You are probably expecting it to behave like this:
您可能希望它的行为如下：
SELECT
…
FROM (a, b) LEFT JOIN dkcd ON …
WHERE …
that is, the combination of tables aand bis joined with the table dkcd. In fact, what's happening is
即表的组合a和b与表的连接dkcd。事实上，正在发生的事情是
SELECT
…
FROM a, (b LEFT JOIN dkcd ON …)
WHERE …
that is, as you may already have understood, dkcdis joined specifically against band only b, then the result of the join is combined with aand filtered further with the WHEREclause. In this case, any reference to ain the ONclause is invalid, ais unknown at that point. That is why you are getting the error message.
也就是说，正如您可能已经理解的那样，dkcd是专门针对band only 连接的b，然后连接的结果a与WHERE子句组合并进一步过滤。在这种情况下，子句a中的任何引用ON都是无效的，此时a是未知的。这就是您收到错误消息的原因。
If I were you, I would probably try to rewrite this query, and one possible solution might be:
如果我是你，我可能会尝试重写此查询，一种可能的解决方案可能是：
SELECT DISTINCT
a.maxa,
b.mahuyen,
a.tenxa,
b.tenhuyen,
ISNULL(dkcd.tong, 0) AS tongdkcd
FROM phuongxa a
INNER JOIN quanhuyen b ON LEFT(a.maxa, 2) = b.mahuyen
LEFT OUTER JOIN (
SELECT
maxa,
COUNT(*) AS tong
FROM khaosat
WHERE CONVERT(datetime, ngaylap, 103) BETWEEN 'Sep 1 2011' AND 'Sep 5 2011'
GROUP BY maxa
) AS dkcd ON dkcd.maxa = a.maxa
WHERE a.maxa <> '99'
ORDER BY a.maxa
Here the tables aand bare joined first, then the result is joined to dkcd. Basically, this is the same query as yours, only using a different syntax for one of the joins, which makes a great difference: the reference a.maxain the dkcd's join condition is now absolutely valid.
这里首先连接表a和b，然后将结果连接到dkcd。基本上，这是相同的查询是你的，只是使用不同的语法的连接，这使得有很大的差别之一：参考a.maxa中dkcd的连接条件是现在绝对有效。
As @Aaron Bertrand has correctly noted, you should probably qualify maxawith a specific alias, probably a, in the ORDER BYclause.
正如@Aaron Bertrand 正确指出的那样，您可能应该在子句中maxa使用特定别名（可能是 ）进行限定。aORDER BY
回答by Bobs
Sometimes this error occurs when you use your schema (dbo) in your query in a wrong way.
有时，当您在查询中以错误的方式使用架构 (dbo) 时会发生此错误。
for example if you write:
例如，如果你写：
select dbo.prd.name
from dbo.product prd
you will get the error.
你会得到错误。
In this situations change it to:
在这种情况下将其更改为：
select prd.name
from dbo.product prd
回答by Alexander Zaldostanov
if you have given alies name change that to actual name
如果您给了别名，请将其更改为实际名称
for example
例如
SELECT
A.name,A.date
FROM [LoginInfo].[dbo].[TableA] as A
join
[LoginInfo].[dbo].[TableA] as B
on [LoginInfo].[dbo].[TableA].name=[LoginInfo].[dbo].[TableB].name;
change that to
将其更改为
SELECT
A.name,A.date
FROM [LoginInfo].[dbo].[TableA] as A
join
[LoginInfo].[dbo].[TableA] as B
on A.name=B.name;
回答by Pavel M.
I was struggling with the same error message in SQL SERVER, since I had multiple joins, changing the order of the joinssolved it for me.
我在 SQL SERVER 中遇到了相同的错误消息，因为我有多个连接，更改连接的顺序为我解决了这个问题。
回答by Hashim Akhtar
In my case the issue turned out to be the alias name I had given to the table. "oa" seems to be not acceptable for SQL Server.
在我的情况下，问题原来是我给表提供的别名。SQL Server 似乎不能接受“oa”。
回答by xbmono
I was having the same error from JDBC. Checked everything and my query was fine. Turned out, in where clause I have an argument:
我在 JDBC 中遇到了同样的错误。检查了一切，我的查询很好。原来，在 where 子句中，我有一个论点：
where s.some_column = ?
And the value of the argument I was passing in was null. This also gives the same error which is misleading because when you search the internet you end up that something is wrong with the query structure but it's not in my case. Just thought someone may face the same issue
我传入的参数值为空。这也会产生同样的误导性错误，因为当您搜索互联网时，您最终会发现查询结构有问题，但在我的情况下并非如此。只是想有人可能会面临同样的问题
回答by SauerTrout
What worked for me was to change my WHERE clause into a SELECT subquery
对我有用的是将我的 WHERE 子句更改为 SELECT 子查询
FROM:
从：
DELETE FROM CommentTag WHERE [dbo].CommentTag.NoteId = [dbo].FetchedTagTransferData.IssueId
TO:
到：
DELETE FROM CommentTag WHERE [dbo].CommentTag.NoteId = (SELECT NoteId FROM FetchedTagTransferData)
回答by Tore Aurstad
I was also struggling with this error and ended up with the same strategy as the answer. I am including my answer just to confirm that this is a strategy that should work.
我也在为这个错误而苦苦挣扎，最终得到了与答案相同的策略。我包括我的答案只是为了确认这是一个应该有效的策略。
Here is an example where I do first one inner join between two tables I know got data and then two left outer joins on tables that might have corresponding rows that can be empty. You mix inner joins and outer joins to get results with data accross tables instead of doing the default comma separated syntax between tables and miss out rows in your desired join.
这是一个示例，我首先在两个我知道有数据的表之间执行一个内部连接，然后在可能具有可以为空的相应行的表上执行两个左外部连接。您可以混合使用内连接和外连接来获取跨表数据的结果，而不是在表之间执行默认的逗号分隔语法，从而遗漏所需连接中的行。
use somedatabase
go
select o.operationid, o.operatingdate, p.pasid, p.name as patientname, o.operationalunitid, f.name as operasjonsprogram, o.theaterid as stueid, t.name as stuenavn, o.status as operasjonsstatus from operation o
inner join patient p on o.operationid = p.operationid
left outer join freshorganizationalunit f on f.freshorganizationalunitid = o.operationalunitid
left outer join theater t on t.theaterid = o.theaterid
where (p.Name like '%Male[0-9]%' or p.Name like '%KFemale [0-9]%')
First: Do the inner joins between tables you expect to have data matching. Second part: Continue with outer joins to try to retrieve data in other tables, but this will not filter out your result set if table outer joining to has not got corresponding data or match on the condition you set up in the on predicate / condition.
第一：在您希望进行数据匹配的表之间进行内部连接。第二部分：继续使用外连接尝试检索其他表中的数据，但是如果外连接没有得到相应的数据或匹配您在 on 谓词/条件中设置的条件，这将不会过滤掉您的结果集。
回答by Zolfaghari
My error was to use a field that did not exist in table.
我的错误是使用了表中不存在的字段。
table1.field1 => is not exist
table1.field1 => 不存在
table2.field1 => is correct
table2.field1 => 是正确的
Correct your Table Name.
更正您的表名。
my error occurred because of using WITH
我的错误是因为使用 WITH
WITH RCTE AS (
SELECT...
)
SELECT RCTE.Name, ...
FROM
RCTE INNER JOIN Customer
ON RCTE.CustomerID = Customer.ID
when used in join with other tables ...
当与其他表连接使用时...
回答by Tadej
Did you forget to join some tables? If not then you probably need to use some aliases.
你忘记加入一些表了吗？如果没有，那么您可能需要使用一些别名。
