合并两个MySQL查询

Question

合并两个MySQL查询

3

我有两个问题：

SELECT     sql_cache distinct p.products_image, 
           p.products_subimage1, 
           pd.products_name, 
           p.products_quantity, 
           p.products_model, 
           p.products_ordered, 
           p.products_id, 
           p.products_price, 
           p.products_date_added, 
           p.products_weight, 
           p.products_length, 
           p.products_width, 
           p.products_height, 
           p.products_tax_class_id, 
           p.products_status, 
           IF(s.status, s.specials_new_products_price, NULL)             AS specials_new_products_price,
           IF(s.status, s.specials_new_products_price, p.products_price) AS final_price 
FROM       products p 
LEFT JOIN  specials s 
ON         p.products_id = s.products_id 
LEFT JOIN  products_to_categories p2c 
ON         p.products_id=p2c.products_id 
LEFT JOIN  products_description pd 
ON         p.products_id=pd.products_id 
INNER JOIN filter_association_products fap 
ON         p.products_id =fap.products_id 
LEFT JOIN  products_attributes pa 
ON         p.products_id = pa.products_id 
WHERE      p.products_status = '1' 
AND        date_sub(curdate(),INTERVAL 3000 day) <= p.products_date_added 
AND        fap.filter_id = 126 
ORDER BY   p.products_date_added DESC, 
           pd.products_name

这给我提供了52行产品的结果。

另外一个查询完全相同，唯一的区别是:

AND fap.filter_id = 130

这个查询结果给我展示了4行数据。

这些行/产品之间的共同点是，其中3个拥有filter_id 130的行也拥有filter_id 126，因此我想修改查询以仅返回拥有所有指定filter_id（甚至更多，取决于应用的filter_id）的产品结果。

我已经尝试过

... 
AND FIND_IN_SET(fap.filter_id,'126', '130') 
ORDER BY p.products_date_added DESC, pd.products_name

但是我得到了53行/个产品的结果，这意味着它显示了所有具有任一筛选器的产品，而我在这种情况下寻找的结果仅为具有 filter_id 的3行。

重写查询以获得正确结果的最佳方法是什么？

- Nikita 웃

1

可能是SQL用于在连接中应用条件到多个行的重复问题。 - Clockwork-Muse

3个回答

1

在WHERE中对filter_association_products和filter进行两次LEFT JOIN，如下所示：

 SELECT     sql_cache distinct p.products_image, 
               p.products_subimage1, 
               pd.products_name, 
               p.products_quantity, 
               p.products_model, 
               p.products_ordered, 
               p.products_id, 
               p.products_price, 
               p.products_date_added, 
               p.products_weight, 
               p.products_length, 
               p.products_width, 
               p.products_height, 
               p.products_tax_class_id, 
               p.products_status, 
               IF(s.status, s.specials_new_products_price, NULL)             AS specials_new_products_price,
               IF(s.status, s.specials_new_products_price, p.products_price) AS final_price 
    FROM       products p 
    LEFT JOIN  specials s 
    ON         p.products_id = s.products_id 
    LEFT JOIN  products_to_categories p2c 
    ON         p.products_id=p2c.products_id 
    LEFT JOIN  products_description pd 
    ON         p.products_id=pd.products_id 
    LEFT JOIN filter_association_products fap1 
    ON         p.products_id =fap1.products_id 
    LEFT JOIN filter_association_products fap2 
    ON         p.products_id =fap2.products_id 
    LEFT JOIN  products_attributes pa 
    ON         p.products_id = pa.products_id 
    WHERE      p.products_status = '1' 
    AND        date_sub(curdate(),INTERVAL 3000 day) <= p.products_date_added 
    AND        fap1.filter_id = 126 
    AND        fap2.filter_id = 130 
    ORDER BY   p.products_date_added DESC, 
               pd.products_name

另一种方法（子查询）：

 SELECT     sql_cache distinct p.products_image, 
               p.products_subimage1, 
               pd.products_name, 
               p.products_quantity, 
               p.products_model, 
               p.products_ordered, 
               p.products_id, 
               p.products_price, 
               p.products_date_added, 
               p.products_weight, 
               p.products_length, 
               p.products_width, 
               p.products_height, 
               p.products_tax_class_id, 
               p.products_status, 
               IF(s.status, s.specials_new_products_price, NULL)             AS specials_new_products_price,
               IF(s.status, s.specials_new_products_price, p.products_price) AS final_price 
    FROM       products p 
    LEFT JOIN  specials s 
    ON         p.products_id = s.products_id 
    LEFT JOIN  products_to_categories p2c 
    ON         p.products_id=p2c.products_id 
    LEFT JOIN  products_description pd 
    ON         p.products_id=pd.products_id 
    LEFT JOIN  products_attributes pa 
    ON         p.products_id = pa.products_id 
    WHERE      p.products_status = '1' 
    AND        date_sub(curdate(),INTERVAL 3000 day) <= p.products_date_added 
    AND  126 in (select fap1.filter_id from filter_association_products fap1 
                 where p.products_id = fap1.products_id)
    AND  130 in (select fap2.filter_id  from  filter_association_products fap2 
                 where p.products_id = fap2.products_id)
    ORDER BY   p.products_date_added DESC, 
               pd.products_name

- Mikey

带有 WHERE 条件的 LEFT JOIN 等于 INNER JOIN。在长期内，这也不是可扩展的，因为需要额外连接的数量。 - Clockwork-Muse

这是真的，但最简单的纯SQL可以满足给定的请求。此外，内连接与左连接加where子句是一种口味选择。 - Mikey

不，这是一个错误，并且对未来的维护者构成了危险。它还可能会对低效的关系型数据库管理系统（RBMS）造成性能损失。在您的代码中要清晰明了。您是否确定需要该关系？如果不存在，则应将其排除在外。那么请使用“INNER JOIN”（最好将所有相关关系都放在“ON”子句中）。如果数据存在，您是否需要该关系，但如果不存在则只需要“主要”数据？请使用“LEFT JOIN”。如果在代码审查中出现此类情况，我会要求进行更改。 - Clockwork-Muse

谢谢。那么，在考虑到有许多filter_id的情况下，这个页面上的三种方法中哪一种最适合可扩展性和性能？ - Nikita 웃

我不会太在意性能问题 - 如果您在性能方面有问题，请测试所有三个：采用@Gordon Linoff的答案来提高可扩展性 ;) - Mikey

显示剩余2条评论

1

虽然Mikey和Gordon Linoff的答案都很好，但我更喜欢Gordon的方法，因为它更具可扩展性。然而，他的答案有一些语法错误，并且转换为JOIN没有产生任何结果，所以我不得不将它们修改回LEFT JOINs，正如Mikey建议的那样。（感谢你们两个）。这是最终可行的查询：

SELECT     sql_cache distinct p.products_image, 
           p.products_subimage1, 
           pd.products_name, 
           p.products_quantity, 
           p.products_model, 
           p.products_ordered, 
           p.products_id, 
           p.products_price, 
           p.products_date_added, 
           p.products_weight, 
           p.products_length, 
           p.products_width, 
           p.products_height, 
           p.products_tax_class_id, 
           p.products_status, 
           IF(s.status, s.specials_new_products_price, NULL)             AS specials_new_products_price,
           IF(s.status, s.specials_new_products_price, p.products_price) AS final_price 
FROM       products p 
LEFT JOIN  specials s 
ON         p.products_id = s.products_id 
LEFT JOIN  products_to_categories p2c 
ON         p.products_id=p2c.products_id 
LEFT JOIN  products_description pd 
ON         p.products_id=pd.products_id 
INNER JOIN filter_association_products fap 
ON         p.products_id =fap.products_id 
LEFT JOIN  products_attributes pa 
ON         p.products_id = pa.products_id 

WHERE p.products_status = '1' 
AND    date_sub(curdate(),INTERVAL 3000 day) <= p.products_date_added 
AND      fap.filter_id IN (126, 130)
GROUP BY p.products_id
HAVING COUNT(DISTINCT fap.filter_id) = 2;

- Nikita 웃

这里应该不需要使用 DISTINCT；GROUP BY 会确保你只获取一个 product_id，这也会使来自该表的数据唯一。如果你从其他表中的其中一个表中获取多个值（例如名称），那么在它们不同的情况下它对你没有帮助（你将得到两个不同名称的行）；你需要选择一个特定的行（通常是“最近的”）。你没有使用 products_to_categories 或 products_attributes 中的列，因此可以将它们从查询中删除。 - Clockwork-Muse

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

使用GROUP BY来获取您想要的产品信息。您也可以加入其他信息，但这是基本查询：

SELECT p.*
FROM products p JOIN
     specials s 
     ON p.products_id = s.products_id JOIN
     products_to_categories p2c 
     ON p.products_id = p2c.products_id JOIN
     products_description pd 
     ON p.products_id = pd.products_id JOIN
     filter_association_products fap 
     ON p.products_id = fap.products_id JOIN
     products_attributes pa 
     ON p.products_id = pa.products_id 
WHERE p.products_status = '1' AND
      date_sub(curdate(),INTERVAL 3000 day) <= p.products_date_added AND
      fap.filter_id IN (126, 130)
GROUP BY p.products_id
HAVING COUNT(DISTINCT fap.filter_id) = 2;  -- make sure both match

我认为大多数表格（至少在filter_association_products之前的所有内容都被WHERE子句转换为内部联接）没有使用LEFT JOIN的理由，因此我将它们改成了内部联接。