Firebird到MySQL查询的迁移-选择内部联接子查询 [英] Firebird to MySQL query migration - Select Inner Join Subquery

问题描述

我有一个可在我们的Firebird SQL数据模块中使用的查询.

I have a query that worked in our Firebird SQL data module.

我们迁移到MySQL，除此查询外，我所有的查询均无问题.

We migrated to MySQL and all my queries work no problem except for this one.

请帮助我解决此问题.我收到错误消息:

Please help me fix this. I get an error:

执行失败. "on子句"中的未知列"part.id"

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

我的Firebird查询:

My Firebird query:

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外，所有表都在数据库中.

All tables are in the database except for lowestcost and highestcost which were created in the sub-queries.

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

Hopefully my request is clearly written. But to summarize - I need this working Firebird query to be migrated to work in MySQL.

为什么这可以在Firebird中工作，但不能在MySQL中工作?

Why would this work in Firebird but not MySQL?

推荐答案

问题是在mySQL中，

The problem is that in mySQL the comma operator has lower precedence than the join operator, therefore the product inner join (subquery) on part.id = highestcost.partid inner join (subquery) on part.id = lowestcost.partid joins are evaluated before the part table is joined in the expression, hence the error message.

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

Replace the comma operators with simple join operators and move the join conditions from the where clause to on clauses and all will be fine:

...
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中没有语法错误，它们也会产生不同的结果.

If you have more such queries where you mix comma operator and explicit joins, then you should check them out because they may produce different results even if there was not syntax error in MySQL.

这篇关于Firebird到MySQL查询的迁移-选择内部联接子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！