SQL MIN()和通过...分组 [英] SQL MIN() & GROUP BY

问题描述

我正在寻找一种更优雅的解决方案，以解决MySQL MIN()与GROUP BY结合使用的问题.

I'm searching for a more elegant solution to a problem I'm having with MySQL MIN() used in conjunction with GROUP BY.

我有两个表:

1. 产品(id，group_id)
2. product_attributes(product_id，attribute_id，值)

我希望从某个产品组的每个金属组(attribute_id = 40)中选择最便宜的金属.

I wish to select the cheapest metal out of each metal group (attribute_id = 40) from a certain product group.

以下查询有效，但我可以肯定它可以写得更好.

The following query works but I'm quite certain it could have been written much better.

SELECT metal.product_id, metal.value AS `metal`, `price`.value AS `price`
FROM
    product_attributes metal
INNER JOIN
    (
        SELECT metal.value AS `metal`, MIN(price.value) AS `price`
        FROM 
            product_attributes metal
        LEFT JOIN 
            product_attributes price
        ON metal.product_id = price.product_id
        INNER JOIN products p
        ON p.id = metal.product_id
        WHERE
            metal.attribute_id = 40 AND
            price.attribute_id = 37 AND
            p.group_id = 1
        GROUP BY
            metal.value
        ORDER BY metal ASC, price DESC
    ) AS d ON
    d.metal = metal.value
LEFT JOIN
    product_attributes price ON
    metal.product_id = price.product_id AND price.attribute_id = 37
INNER JOIN
    products p ON
    p.id = metal.product_id
WHERE
    metal.attribute_id = 40
    AND d.price = price.value
    AND p.group_id = 1
GROUP BY
    metal.value
ORDER BY
    metal.value ASC,
    price.value DESC;

推荐答案

解决方案最终比我要简单.我已经在内部查询中按价格对数据进行了排序，因此在GROUP BY之前强制进行排序，这样我就可以准确地获得所需的东西(每个金属组中最便宜的产品).

The solution finally was simpler than I though. I've ordered my data by price in an inner query, thus forcing a sort before GROUP BY, this way I get exactly what I need (the cheapest product from each metal group).

SELECT * FROM
            (
                SELECT metal.enum_id AS `metal`, metal.product_id FROM product_attributes metal
                LEFT JOIN product_attributes price ON metal.product_id = price.product_id AND price.attribute_id = 37
                INNER JOIN products p ON p.id = metal.product_id
                WHERE
                    metal.attribute_id = 40 AND
                    p.group_id = 1 AND
                    p.id NOT IN (SELECT DISTINCT product_id FROM product_attributes WHERE (attribute_id=162 OR attribute_id=204 OR attribute_id=205) AND `value`=1)
                ORDER BY price.value+'0' ASC 
            ) AS s
            GROUP BY metal

这篇关于SQL MIN()和通过...分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！