sql - 如何在 ORDER BY 和 LIMIT 之后获得一列的总和？

Question

sql：

    SELECT instock.rating
    FROM instock
    JOIN products
    ON instock.code_id=products.code
    WHERE products.brand="Adidas"
    ORDER BY -instock.rating
    LIMIT 5;

例如：返回：5 5 6 6 7

在 Where、Order By 和 Limit 之后，我需要 SUM(i.rating)，返回 5+ 5+ 6+ 6+ 7。

ORDER BY 响应后包含大约 200 行。我的尝试示例：1：

    SELECT SUM(instock.rating) 
    FROM instock
    JOIN products
    ON instock.code_id=products.code
    WHERE products.brand="Adidas"
    ORDER BY -instock.rating
    LIMIT 5; 

返回，值：3000，所以，SUM 所有查询。2：

    SELECT SUM(DISTINCT instock.rating)
    FROM instock
    JOIN products
    ON instock.code_id=products.code
    WHERE products.brand="Adidas"
    ORDER BY -instock.rating
    LIMIT 5;

返回：5+ 6+ 7== 19，所以，SUM 单 i。

如何从第一个 sql 示例中获取值：5+ 5+ 6+ 6+ 7 = 29？

标签： sqlselectsumsql-order-by

Answer 1

SELECT SUM(rate)
FROM (SELECT i.rating as rate
    FROM instock_nagornaya AS i 
    JOIN products AS p
    ON code_id=code
    WHERE p.brand="Adidas"
    ORDER BY -i.rating
    LIMIT 5);

可能会更快解决？