mysql - two - w3c join




Firebird到MySQL查詢遷移-選擇Inner Join Subquery (2)

不要混合顯式和隱式聯接
避免在列名和表名中使用相同的別名(在這個例子中,參考t1和t2),避免使用子查詢表名

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 
INNER JOIN vendorparts on vendor.id = vendorparts.vendorid AND vendorparts.lastcost <> 0 
INNER JOIN part on vendorparts.partid = part.id and 
INNER JOIN product on product.partid = part.id
INNER JOIN (SELECT vendorparts.partid,
                          Max(vendorparts.lastcost) AS Highestcost
                   FROM   vendorparts
                   GROUP  BY vendorparts.partid)  t1
               ON part.id = t1.partid
INNER JOIN (SELECT vendorparts.partid,
                          Min(vendorparts.lastcost) AS Lowestcost
                   FROM   vendorparts
                   GROUP  BY vendorparts.partid)  t2
               ON part.id = t2.partid

我有一個查詢,在我們的Firebird SQL數據模塊。

我們遷移到MySQL和我的所有查詢工作沒有問題,除了這一個。

請幫我解決這個問題。 我得到一個錯誤:

無法執行。 'on子句'中的未知列'part.id'

我的火鳥查詢:

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 

除了在子查詢中創建的lowestcostlowestcost之外,所有表都在數據庫中。

希望我的要求寫得很清楚。 但總結 - 我需要這個工作的火鳥查詢被遷移到MySQL的工作。

為什麼這個工作在Firebird而不是MySQL?


此查詢也不能在Firebird 3.0和更高版本中使用(請參閱支持混合語法連接已過 )。 原因是你正在將SQL-89風格的聯接與SQL-92風格的聯接相結合。

你需要重寫查詢以在任何地方使用顯式連接,所以:

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




firebird