这是另一种方法：

select amount, count(*) AS quantity
from
(
    SELECT participations.member_number, COUNT(participations.member_number) AS amount
    FROM participations
    LEFT JOIN members on participations.member_number=members.member_number 
    WHERE members.active='1'
    GROUP BY participations.member_number
)
DT
group by amount
order by amount;

说明：
使用WHERE：连接后。记录将在连接后进行筛选。
使用ON：联接前。联接前将筛选记录（来自右表）。
对于INNER JOIN，这些子句是等效的。

问题可能出在LEFT JOIN语句中的AND子句。您可以尝试将AND子句移到WHERE子句中，如下所示：

FROM participations
LEFT JOIN members ON participations.member_number=members.member_number
WHERE members.active='1'
GROUP BY participations.member_number, members.active
) DT
GROUP BY amount
ORDER BY amount

这样，AND子句将应用于从参与表和成员表中筛选出的数据，而不是应用于整个数据。