mysql - 加入三个表并检查记录

Question

我想连接三个表并检查另外两个表是否有用户记录。

表格:

table1
id | username | is_active

table2
id | userid | amount

table3
id | userid | amount

我想获取并计算 'is_active' = 1 且表 2 和表 3 上没有记录的用户

我正在尝试这个:

SELECT c.id,c.is_active, 
       COUNT(c.id) AS count,
       COUNT(a.userid) AS count1,
       COUNT(b.userid) AS count2
FROM   `tbl_members` c 
       LEFT JOIN `table1` b 
              ON c.`id` = b.`userid` 
       LEFT JOIN `table2` a 
              ON b.`userid` = a.`userid` 
WHERE  c.`is_active` = 1 
GROUP BY c.id

Answer 1

这个怎么样？

select count(*) as count from
(SELECT distinct c.id
FROM   `tbl_members` c 
       LEFT JOIN `table1` b 
              ON c.`id` = b.`userid` 
       LEFT JOIN `table2` a 
              ON c.id = a.`userid` 
WHERE  c.`is_active` = 1 and a.userid is null and b.userid is null) s1

这会计算具有 is_active 集且没有对应的 a.userid 或 b.userid 的唯一 c.id。

事实上，您可以省略 'distinct'，假设 c.id 是 tbl_members 的主键(因此是唯一的)。这将使它更简单:

select count(*) 
FROM   `tbl_members` c 
       LEFT JOIN `table1` b 
              ON c.`id` = b.`userid` 
       LEFT JOIN `table2` a 
              ON c.id = a.`userid` 
WHERE  c.`is_active` = 1 and a.userid is null and b.userid is null