主要问题是Relation部分的"WHERE IN",它将找到的Rows相乘
例如,我添加了GROUP_CONCT以更好地查看正在发生的事情:
SELECT COUNT(*),
GROUP_CONCAT(sections.name SEPARATOR '; ')
FROM users
LEFT JOIN users_sections ON (users.id = users_sections.user_id)
LEFT JOIN sections ON (users_sections.section_id = sections.id)
GROUP BY users.id
Finds:
'2', 'Registered; admins'
'3', 'Registered; admins; dudes'
'1', 'Registered'
找到了3行,什么是正确的,但结果是无用的
现在添加WHERE IN部分:
SELECT COUNT(*),
GROUP_CONCAT(sections.name SEPARATOR '; ')
FROM users
LEFT JOIN users_sections ON (users.id = users_sections.user_id)
LEFT JOIN sections ON (users_sections.section_id = sections.id)
WHERE sections.id IN (2,3)
GROUP BY users.id
Finds:
'2', 'Registered; admins'
'2', 'Registered; admins'
'1', 'Registered'
甚至是正确但无用的。我需要一个单独的"3"作为COUNT结果
好的,那么,让我们删除GROUP_CONCT和。。。祈祷
SELECT COUNT(*)
FROM users
LEFT JOIN users_sections ON (users.id = users_sections.user_id)
LEFT JOIN sections ON (users_sections.section_id = sections.id)
WHERE sections.id IN (2,3)
GROUP BY users.id
Find:
'2'
'2'
'1'
和以前一样。让我们试试这个:
SELECT COUNT(*) > 0
...
Find:
'1'
'1'
'1'
嘿,看起来不错!让我们尝试一下(希望最后):
SELECT SUM(COUNT(*) > 0)
...
Find:
Invalid use of group function
太伤心了
我怎么才能得到伯爵,还是不可能?
您想要做的(如果我读得正确的话)是计算应用特定条件的不同用户id。我无法从中找出真正的问题,但我认为你需要它:
SELECT
COUNT( DISTINCT users.id)
FROM
users
LEFT JOIN users_sections ON (users.id = users_sections.user_id)
LEFT JOIN sections ON (users_sections.section_id = sections.id)
WHERE
sections.id IN (2,3)