sql - 多部分标识符无法绑定(The multi-part identifier could not be bound)

Question

I've seen similar errors on SO, but I don't find a solution for my problem.

(我在SO上看到过类似的错误，但是找不到解决我问题的方法。)

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.

(当我执行此查询时，错误结果是： 不能绑定多部分标识符“ a.maxa”。)

Why?

(为什么？)
P/s: if i divide the query into 2 individual query, it run ok.

(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;

  ask by PhamMinh translate from so

Answer 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关键字实现的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 .

(也就是说，表a和b的组合与表dkcd 。)

In fact, what's happening is

(实际上，正在发生的是)

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.

(即，作为你可能已经理解， dkcd具体针对接合b并且只b ，则结果的连接与结合a和与所述进一步过滤WHERE子句。)

In this case, any reference to a in the ON clause is invalid, a is unknown at that point.

(在这种情况下，在ON子句中对a任何引用都是无效的，此时a是未知的。)

That is why you are getting the error message.

(这就是为什么您收到错误消息的原因。)

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 .

(在这里，表a和b首先被连接，然后结果被连接到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.maxa dkcd条件中的引用a.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来限定maxa 。)