我有一个在我们的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
除了在子查询中创建的最低成本和最高成本之外,所有表都在数据库中.
希望我的要求写得清楚.但总而言之 – 我需要将这个工作的Firebird查询迁移到MySQL中.
为什么这会在Firebird中运行而不是在MySQL中运行？
