MySQL子查询忽略AND members.active='1'条件问题求助

核心原因 #

你这里用了LEFT JOIN，它的特性是会保留左表（participations）的所有记录，哪怕右表（members）找不到符合members.active='1'的匹配行。而子查询里统计的是COUNT(participations.member_number)——这个字段来自左表，不管右表有没有匹配成功，左表的该字段都存在（除非本身为NULL，但你的场景里显然不是），所以COUNT的结果完全不受右表匹配条件的影响，导致members.active='1'相当于没生效。

对应解决方案 #

根据你的业务需求，有两种处理方式：

1. 只统计活跃会员的参与记录

如果你的目标是只保留属于active='1'会员的参与数据，把条件从JOIN的ON子句移到WHERE子句，或者直接改成INNER JOIN：

select amount, count(*) AS quantity
from
(
SELECT participations.member_number, COUNT(participations.member_number) AS amount
FROM participations
INNER 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

或者用LEFT JOIN加WHERE过滤（效果和上面一致）：

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

这两种写法都会过滤掉所有不属于活跃会员的参与记录。

2. 保留所有参与记录，但仅统计活跃会员的参与次数

如果需要保留所有参与记录，同时只把匹配到活跃会员的次数计入统计（非活跃会员的参与次数显示为0），可以把COUNT的字段改成右表的members.member_number——LEFT JOIN中未匹配的行该字段为NULL，COUNT会自动忽略NULL值：

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

这样非活跃会员的参与记录在子查询里的amount会是0，最终结果会包含amount=0的分组。

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