除了之前与获取所有子类别 ID 相关的问题之外,我使用此查询来提取所有有效categories_id的列表。
SELECT categories_id
FROM (SELECT * FROM categories ORDER BY categories_id)
products_sorted, (SELECT @pv := '151') initialisation
WHERE find_in_set(parent_id, @pv)
AND length(@pv := concat(@pv, ',', categories_id))
仅此查询即可正常工作。
然后,我进行了一个简单的查询来检查今天是否已将产品添加到数据库中
SELECT * FROM products
WHERE DATE(products_date_added) = DATE(NOW())
难题的最后一部分是检查任何退回产品的master_categories_id是否与第一个查询中返回的categories_id匹配。所以我尝试了
SELECT * FROM products
WHERE DATE(products_date_added) = DATE(NOW())
AND master_categories_id
IN(SELECT categories_id AS master_categories_id
FROM (SELECT * FROM categories ORDER BY categories_id)
products_sorted, (SELECT @pv := '151') initialisation
WHERE find_in_set(parent_id, @pv)
AND length(@pv := concat(@pv, ',', categories_id)))
我没有收到错误,即使我知道某些产品具有存在于其他查询中的master_categories_id,我也会得到零结果。 我做错了什么?
你应该把它写成一个连接而不是WHERE ... IN
,因为当@pv变量在WHERE
子句中重复使用时,它的重新分配顺序可能是错误的。
SELECT DISTINCT p.* FROM products AS p
JOIN (
SELECT categories_id AS master_categories_id
FROM (SELECT * FROM categories ORDER BY categories_id)
products_sorted, (SELECT @pv := '151') initialisation
WHERE find_in_set(parent_id, @pv)
AND length(@pv := concat(@pv, ',', categories_id))
) AS c
ON c.master_categories_id = p.master_categories_id
WHERE DATE(p.products_date_added) = DATE(NOW())
与子查询联接应该只执行一次,就像单独使用查询一样。