不能绑定多部分标识符。

I've seen similar errors on SO, but I don't find a solution for my problem. I have a SQL query like:

我也看到过类似的错误，但我没有找到解决问题的方法。我有一个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;

13 个解决方案

#1

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 JOIN keyword) take precedence over implicit ones (the 'comma' joins, where the join condition is specified in the WHERE clause).

问题是，显式连接(使用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 a and b is 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, dkcd is joined specifically against b and only b, then the result of the join is combined with a and filtered further with the WHERE clause. In this case, any reference to a in the ON clause is invalid, a is unknown at that point. That is why you are getting the error message.

也就是说，正如您可能已经理解的，dkcd是专门针对b和b的，然后join的结果与a结合，并随着WHERE子句进一步过滤。在本例中，对ON子句的任何引用都是无效的，在这一点上是未知的。这就是为什么要获取错误消息的原因。

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 a and b are 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.maxa in the dkcd's join condition is now absolutely valid.

在这里，表a和b首先连接，然后结果与dkcd连接。基本上，这和您的查询是相同的，只是对其中一个连接使用不同的语法，这就产生了很大的差别:引用a。在dkcd的联接条件下的maxa现在是绝对有效的。

As @Aaron Bertrand has correctly noted, you should probably qualify maxa with a specific alias, probably a, in the ORDER BY clause.

正如@Aaron Bertrand所正确指出的，您应该在ORDER BY子句中使用特定的别名(可能是a)来限定maxa。

#2

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

#3

if you have given alies name change that to actual name

如果您已经给定了alies名称，请将其更改为实际名称。

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;

#4

I was struggling with the same error message in SQL SERVER, since I had multiple joins, changing the order of the joins solved it for me.

我在SQL SERVER中遇到了同样的错误消息，因为我有多个连接，改变了连接的顺序为我解决了这个问题。

#5

I'm new to SQL, but came across this issue in a course I was taking and found that assigning the query to the project specifically helped to eliminate the multi-part error. For example the project I created was CTU SQL Project so I made sure I started my script with USE [CTU SQL Project] as my first line like below.

我是SQL新手，但是在我的课程中遇到了这个问题，并发现将查询分配给项目可以帮助消除多部分错误。例如，我创建的项目是CTU SQL项目，所以我确定我的脚本是使用[CTU SQL项目]作为我的第一行。

USE [CTU SQL Project]
SELECT Advisors.First_Name, Advisors.Last_Name...and so on.

#6

If this error happens in an UPDATE, double-check the JOIN on the table with the column/field that is causing the error.

如果此错误发生在更新中，请用导致错误的列/字段再次检查表中的联接。

In my case this was due to the lack of the JOIN itself, which generated the same error due to an unknown field (as Andriy pointed out).

在我的例子中，这是由于连接本身的缺乏，这导致了一个未知字段(Andriy指出)的相同错误。

#7

SELECT DISTINCT
        phuongxa.maxa ,
        quanhuyen.mahuyen ,
        phuongxa.tenxa ,
        quanhuyen.tenhuyen ,
        ISNULL(dkcd.tong, 0) AS tongdkcd
FROM    phuongxa ,
        quanhuyen
        LEFT OUTER JOIN ( SELECT    khaosat.maxa ,
                                    COUNT(*) AS tong
                          FROM      khaosat
                          WHERE     CONVERT(DATETIME, ngaylap, 103) BETWEEN 'Sep 1 2011'
                                                              AND
                                                              'Sep 5 2011'
                          GROUP BY  khaosat.maxa
                        ) AS dkcd ON dkcd.maxa = maxa
WHERE   phuongxa.maxa <> '99'
        AND LEFT(phuongxa.maxa, 2) = quanhuyen.mahuyen
ORDER BY maxa;

#8

My error was to use a field that did not exist in table.

我的错误是使用了表中不存在的字段。

table1.field1 => is not exist

表1。field1 =>不存在。

table2.field1 => is correct

表二。field1 = >是正确的

Correct your Table Name.

纠正你的表名。

my error occurred because of using 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 ...

当与其他表一起使用时…

#9

Did you forget to join some tables? If not then you probably need to use some aliases.

你忘了加入一些桌子吗?如果不是，那么您可能需要使用一些别名。

#10

Instead you can try joining tables like,

你可以试着加入表格，

select 
  .... 
from 
   dkcd 
     right join 
                a
                  , b

This should work

这应该工作

#11

If none of the above are working, try copying known results that meet one of the search criteria to a temporary table, and then doing the join on that.

如果上面没有工作，尝试将已知的结果复制到一个临时表中，然后执行连接。

#12

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

我传入的参数的值是null。这也会产生同样的错误，这是误导，因为当你搜索互联网的时候，你会发现查询结构有问题，但不是我的情况。只是觉得有人可能会面临同样的问题。

#13

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.

在我的例子中，这个问题变成了我给表的别名。“oa”似乎对SQL Server来说是不可接受的。