SELECT p.*
FROM (
SELECT ProductID,ProductName,ProductCode,SampleRefNum
FROM products
WHERE hidden = 'N'
UNION ALL
SELECT product_variants.ProductID,products.ProductName,product_variants.ProductCode,product_variants.SampleRefNum
FROM product_variants
JOIN products
ON product_variants.ProductID = products.ProductID
) AS p
LEFT JOIN stock_list AS s
ON p.ProductCode = s.ProductCode OR p.SampleRefNum = s.SampleRefNum
WHERE s.ProductCode IS NULL AND p.ProductCode IS NOT NULL AND p.ProductCode <> "" OR s.SampleRefNum IS NULL AND p.SampleRefNum IS NOT NULL AND p.SampleRefNum <> ""
上面的查询非常慢,只有1分钟。如果我只是做工会的一部分的话。或仅使用产品表而不快速合并其表
(我在这里做什么?)产品具有变体,因此我将产品与产品变体结合起来,以获得具有产品代码和样品编号的物品清单。然后,我要加入库存列表(具有来自另一系统的约5万行产品代码和样品参考代码的表),以便我可以获取没有匹配的ProductCode或SampleRefNum的所有记录的列表。
- - - - - - 编辑
我在所有表上都有关于ProductID,ProductCode和SampleRefNum的索引
-快速---
SELECT p.ProductID,p.ProductName,p.ProductCode,p.SampleRefNum FROM products AS p
LEFT JOIN stock_list AS s
ON p.ProductCode = s.ProductCode OR p.SampleRefNum = s.SampleRefNum
WHERE p.Hidden = 'N' AND (s.ProductCode IS NULL AND p.ProductCode IS NOT NULL AND p.ProductCode <> "" OR s.SampleRefNum IS NULL AND p.SampleRefNum IS NOT NULL AND p.SampleRefNum <> "" )
AND (p.ProductID NOT IN(SELECT ProductID FROM product_variants) )
-这需要10秒
SELECT p.*
FROM (
SELECT ProductID,ProductName,ProductCode,SampleRefNum
FROM products
WHERE hidden = 'N'
) AS p
LEFT JOIN stock_list AS s
ON p.ProductCode = s.ProductCode OR p.SampleRefNum = s.SampleRefNum
WHERE s.ProductCode IS NULL AND p.ProductCode IS NOT NULL AND p.ProductCode <> "" OR s.SampleRefNum IS NULL AND p.SampleRefNum IS NOT NULL AND p.SampleRefNum <> ""
一般join
用or
条件将是缓慢的。我建议您使用exists
或更简单的方式重新查询left joins
。但是,您可以通过两个左联接尝试此操作,看看这是否满足您的要求:
SELECT p.*
FROM (SELECT ProductID, ProductName, ProductCode, SampleRefNum
FROM products p
WHERE hidden = 'N'
UNION ALL
SELECT pv.ProductID, p.ProductName, pv.ProductCode, pv.SampleRefNum
FROM product_variants pv JOIN
products p
ON pv.ProductID = p.ProductID
) p LEFT JOIN
stock_list s1
ON p.ProductCode = s1.ProductCode LEFT JOIN
stock_list s2
ON p.SampleRefNum = s2.SampleRefNum
WHERE (s1.ProductCode IS NULL AND p.ProductCode IS NOT NULL AND p.ProductCode <> '') OR
(s2.SampleRefNum IS NULL AND p.SampleRefNum IS NOT NULL AND p.SampleRefNum <> '')
对于这个工作很好,你会希望在两个指标stock_list
:stock_list(ProductCode)
和stock_list(SampleRefNum)
。
注意:如果stock_list
一个键上有多个匹配项,而另一个键上没有多个匹配项,则有可能返回多行。我认为您的原始查询有相同的问题。使用not exists
子句可以解决此问题。
编辑:
该not exists
会是什么样子:
WHERE (NOT EXISTS (select 1
from stock_list s1
where p.ProductCode = s1.ProductCode
) and
p.ProductCode is not null and p.ProductCode <> ''
) AND
(NOT EXISTS (select 1
from stock_list s2
where p.SampleRefNum = s1.SampleRefNum
) and
p.SampleRefNum is not null and p.SampleRefNum <> ''
)
您将从子句中删除join
s from
(这需要相同的索引)。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句