无法绑定多部分标识符

2021-12-01 00:00:00 sql sql-server-2005 sql-server

我在 SO 上看到过类似的错误，但我没有找到解决我的问题的方法.我有一个 SQL 查询，如:

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

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;

当我执行这个查询时，错误结果是:无法绑定多部分标识符a.maxa".为什么?
P/s:如果我将查询分成 2 个单独的查询，它运行正常.

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.

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;

和

SELECT  maxa ,
        COUNT(*) AS tong
FROM    khaosat
WHERE   CONVERT(DATETIME, ngaylap, 103) BETWEEN 'Sep 1 2011'
                                        AND     'Sep 5 2011'
GROUP BY maxa;

推荐答案

您正在混合隐式连接和显式连接.这是允许的，但您需要知道如何正确地做到这一点.

You are mixing implicit joins with explicit joins. That is allowed, but you need to be aware of how to do that properly.

问题是，显式连接(使用 JOIN 关键字实现的连接)优先于隐式连接(逗号"连接，其中连接条件在 WHERE 子句).

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).

以下是您的查询大纲:

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 …

即表a和b的组合与表dkcd连接.事实上，正在发生的事情是

that is, the combination of tables a and b is joined with the table dkcd. In fact, what's happening is

SELECT
  …
FROM a, (b LEFT JOIN dkcd ON …)
WHERE …

也就是说，正如您可能已经理解的那样，dkcd 专门针对 b 并且仅针对 b 进行连接，然后连接的结果与 a 组合并使用 WHERE 子句进一步过滤.在这种情况下，ON 子句中对 a 的任何引用都是无效的，此时 a 是未知的.这就是您收到错误消息的原因.

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.

如果我是你，我可能会尝试重写此查询，一种可能的解决方案可能是:

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

这里首先连接表a和b，然后将结果连接到dkcd.基本上，这是与您的查询相同的查询，只是对其中一个连接使用了不同的语法，这有很大的不同:dkcd 中的引用 a.maxa's 加入条件现在绝对有效.

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.

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

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

相关文章