SQL LEFT JOIN关联多规格表重复行 基于子查询MIN()的解决方案

我是SQL新手，遇到一个无法自行解决的SQL查询问题，具体场景如下：

涉及表结构 #

products表 #

idproducts | product | ... | checked
-------------------------------------
1          | testpr1 | ... | 0
2          | testpr2 | ... | 0
3          | testpr3 | ... | 1
...

productvariations表 #

productid | colorvalue | price_single | price_palette | ...
------------------------------------------------------------
1         | 0          | 12.50        | 11.50         | ...
1         | 1          | 10.25        | 10.00         | ...
2         | 0          | 14.50        | 13.75         | ...
2         | 1          | 13.00        | 11.50         | ...
2         | 1          | 12.50        | 12.00         | ...
...

问题说明 #

我需要通过两次LEFT JOIN关联productvariations表，按colorvalue分别获取对应price_single、price_palette及其他字段。第一次关联colorvalue='0'的逻辑运行正常，因为每个商品仅有一条colorvalue='0'的记录；但第二次关联colorvalue='1'时，同一个商品存在多条colorvalue='1'的记录，导致查询结果出现重复行。

初始查询代码（存在重复行问题） #

SELECT products.*, productvar1.price_single, productvar1.price_palette, productvar2.price_single, productvar2.price_palette, productvar1.price_specialconditions, productvar1.price_colorchange
FROM products 
LEFT JOIN productvariations AS productvar1 
ON products.idproducts = productvar1.product AND productvar1.colorvalue = '0' 
LEFT JOIN productvariations AS productvar2 
ON products.idproducts = productvar2.product AND productvar2.colorvalue = '1' 
WHERE products.checked = '0' LIMIT 200;

尝试修改的错误代码 #

我尝试通过子查询加MIN()聚合的方式解决，但写法存在错误无法运行，错误代码如下：

SELECT products.*, productvar1.price_single, productvar1.price_palette, productvar2.price_single, productvar2.price_palette, productvar1.price_specialconditions, productvar1.price_colorchange
FROM products 
LEFT JOIN productvariations AS productvar1 
ON products.idproducts = productvar1.product AND productvar1.colorvalue = '0' 
LEFT JOIN 
    (SELECT product, MIN(price_single), MIN(price_palette) 
    FROM productvariations AS productvar2
    WHERE productvar2.products = producst.idproducts AND productvar2.colorvalue = '1'
    GROUP BY product) AS productvar2
ON products.idproducts = productvar2.product AND productvar2.colorvalue = '1' 
WHERE products.checked = '0' LIMIT 200;

可运行解决方案 #

以下是验证可用的修正代码：

SELECT products.*, productvar1.price_single, productvar1.price_palette, productvar2.price_single, productvar2.price_palette 
FROM products 
LEFT JOIN productvariations AS productvar1 
ON products.idproducts = productvar1.productid AND productvar1.colorvalue = '0' 
LEFT JOIN 
    (SELECT productvar.productid, MIN(productvar.price_single) as price_single , MIN(productvar.price_palette) as price_palette, productvar.colorvalue 
    FROM productvariations AS productvar INNER JOIN products ON productvar.productid = products.idproducts
    WHERE productvar.productid = products.idproducts AND productvar.colorvalue = '1'
    GROUP BY productvar.productid) AS productvar2
ON products.idproducts = productvar2.productid AND productvar2.colorvalue = '1' 
WHERE products.checked = '0' LIMIT 200;

内容的提问来源于stack exchange，提问作者iMorces