MySQL Join -计数另一个表上的匹配项

Question

我有这样的疑问：

SELECT a.*, b.id AS host_id, COUNT(c.event_id) AS count_joins, COUNT(d.event_id) AS joined
FROM (`events` AS a)
INNER JOIN `users` AS b ON `b`.`id` = `a`.`host_id`
LEFT JOIN `joins` AS c ON `c`.`event_id` = `a`.`id`
LEFT JOIN `joins` AS d ON `d`.`event_id` = `a`.`id` AND d.user_id = 1
WHERE `a`.`date` > '1000-10-10 10:10:10'
GROUP BY `a`.`id`
ORDER BY `a`.`date` ASC
LIMIT 20

它获取所有事件、作者、加入的次数，并检查当前用户(id = 1)是否加入了该事件。

我在检查用户是否加入事件时遇到问题，它返回加入的次数，如果用户加入，则返回1，否则返回0。

Answer 1

试试这个：

SELECT a.*, b.id AS host_id, 
SUM(CASE WHEN c.event_id IS NOT NULL THEN 1 ELSE 0 END) AS count_joins, 
SUM(CASE WHEN c.event_id IS NOT NULL AND c.user_id = 1 THEN 1 ELSE 0 END) AS joined
FROM (`events` AS a)
INNER JOIN `users` AS b ON `b`.`id` = `a`.`host_id`
LEFT JOIN `joins` AS c ON `c`.`event_id` = `a`.`id`
WHERE `a`.`date` > '1000-10-10 10:10:10'
GROUP BY `a`.`id`
ORDER BY `a`.`date` ASC
LIMIT 20

Answer 2

我认为问题是你同时加入了a，b，c和d。因此，每次任何人加入每个事件(a×c)时都会得到一条记录，然后加入用户编号1的记录((a×c)×d)。因此，如果这些表看起来像

事件：{ e1，e2 }加入：{( e1，u1 )，( e1，u2 )，( e1，u3 )，( e2，u2 )，( e2，u4 )}

您的查询(在聚合函数之前)返回

{( e1，u1，u1 )，( e1，u2，u1 )，( e1，u3，u1 )，( e2，u2，null )，( e2，u4，null )}

你可能会得到你想要的东西

select子句在a×c上执行聚合，然后连接回"joins as d“table

• including "c”上的合成域，比如c.user_id == 1 as is_user_1，然后在select子句max( is_user_1 )

中获取max( is_user_1 )