如何查询总折扣之和最小的会员及其对应折扣数据？

错误原因 #

你无法直接在MIN()中嵌套SUM()这类聚合函数，且原查询缺少GROUP BY子句——数据库无法先按会员分组计算总折扣，再对结果取最小值，同时未分组的member_id也无法被正确返回，这就触发了#1111 - Invalid use of group function错误。

可行查询语句 #

方法一：子查询+排序取第一条

先通过子查询算出每个会员的总折扣，再按总折扣升序排序，取第一条即为最小值对应的会员记录：

SELECT `Total Discount`, member_id
FROM (
    SELECT SUM(revenue.total_discount) AS `Total Discount`, membertype.member_id 
    FROM revenue 
    INNER JOIN membertype ON membertype.member_id = revenue.member_id 
    GROUP BY membertype.member_id
) AS member_discounts
ORDER BY `Total Discount` ASC
LIMIT 1;

方法二：HAVING子句匹配最小总折扣

如果存在多个会员总折扣同为最小值的情况，这种方法会返回所有符合条件的记录：

SELECT SUM(revenue.total_discount) AS `Total Discount`, membertype.member_id 
FROM revenue 
INNER JOIN membertype ON membertype.member_id = revenue.member_id 
GROUP BY membertype.member_id
HAVING SUM(revenue.total_discount) = (
    SELECT MIN(total_discount_sum)
    FROM (
        SELECT SUM(total_discount) AS total_discount_sum
        FROM revenue
        GROUP BY member_id
    ) AS discount_sums
);

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