mysql - Firebird 到 MySQL 查询迁移 - 选择内部连接子查询

Question

我有一个在我们的 Firebird SQL 数据模块中有效的查询。

我们迁移到了 MySQL，我的所有查询都没有问题，除了这个。

请帮我解决这个问题。我得到一个错误:

Failed to Execute. Unknown column 'part.id' in 'on clause'

我的 Firebird 查询:

SELECT vendor.name AS "Vendor Name",
   Cast(Cast(vendorparts.lastdate AS date) AS CHAR(10)) AS "Last Date",
   CASE product.price
     WHEN '0' THEN 'CONFIRM'
     WHEN NULL THEN 'CONFIRM'
     ELSE Round(product.price, 2)
   end AS "D-Price",
   Cast(vendorparts.lastcost AS DECIMAL(18, 2)) AS "Last Cost",
   Cast(lowestcost.lowestcost AS DECIMAL(18, 2)) AS "Lowest Cost",
   Cast(highestcost.highestcost AS DECIMAL(18, 2)) AS "Highest Cost",
   part.num AS "Part Number",
   part.description AS "Part Description"

FROM   vendor,
       vendorparts,
       part,
       product
       INNER JOIN (SELECT vendorparts.partid,
                          Max(vendorparts.lastcost) AS Highestcost
                   FROM   vendorparts
                   GROUP  BY vendorparts.partid) AS highestcost
               ON part.id = highestcost.partid
       INNER JOIN (SELECT vendorparts.partid,
                          Min(vendorparts.lastcost) AS Lowestcost
                   FROM   vendorparts
                   GROUP  BY vendorparts.partid) AS lowestcost
               ON part.id = lowestcost.partid
WHERE  vendor.id = vendorparts.vendorid
       AND product.partid = part.id
       AND vendorparts.partid = part.id
       AND vendorparts.lastcost <> 0 

除了在子查询中创建的 lowestcost 和 highestcost 外，所有表都在数据库中。

希望我的要求写得清楚。但总而言之 - 我需要将这个有效的 Firebird 查询迁移到 MySQL 中。

为什么这适用于 Firebird 而不是 MySQL？

Answer 1

问题是在 mySQL 中 comma operator has lower precedence比 join 运算符，因此 product inner join (subquery) on part.id = highestcost.partid inner join (subquery) on part.id = lowestcost.partid 联接是在 part 表加入表达式之前求值，因此出现错误消息。

用简单的 join 运算符替换逗号运算符，并将连接条件从 where 子句移动到 on 子句，一切都会好起来的:

...
FROM   vendor
       inner join vendorparts on vendor.id = vendorparts.vendorid
       inner join part on vendorparts.partid = part.id
       inner join product on product.partid = part.id
       INNER JOIN (SELECT vendorparts.partid,
                          Max(vendorparts.lastcost) AS Highestcost
                   FROM   vendorparts
                   GROUP  BY vendorparts.partid) AS highestcost
               ON part.id = highestcost.partid
       INNER JOIN (SELECT vendorparts.partid,
                          Min(vendorparts.lastcost) AS Lowestcost
                   FROM   vendorparts
                   GROUP  BY vendorparts.partid) AS lowestcost
               ON part.id = lowestcost.partid
WHERE  vendorparts.lastcost <> 0 

如果您有更多混合逗号运算符和显式连接的此类查询，那么您应该检查它们，因为即使在 MySQL 中没有语法错误，它们也可能产生不同的结果。