从1表中计算id，其中id不在第二个表中 [英] count id from 1 table where his id is not in second table

问题描述

如何计算第二个表中没有引用ID的第一个表的所有用户ID？

表1：
 + ----- + ------------ + --------------------- + 
 | id | first_name | created_at | 
 + ----- + ------------ + --------------------- + 
 | 522 | dddd | 2013-04-11 18:44:07 | 
 | 523 | cccc | 2013-03-01 06:19:12 | 
 | 524 | ffff | 2013-03-06 21:07:06 | 
 | 525 | rrrr | 2013-03-06 22:05:08 | 
 | 590 | tttt | 2013-03-16 16:39:59 | 
 | 584 | yyyy | 2013-03-22 14:46:28 | 
 
表2：
 + ----- + --------------------- + ------ --------------- + 
 | id | refer_by_user_id | created_at | 
 + ----- + --------------------- + ----------------- ---- + 
 | 298 | 522 | 2013-02-22 22:19:06 | 
 | 299 | 584 | 2013-03-22 18:27:49 | 
 | 300 | 584 | 2013-03-22 18:48:23 | 
 | 301 | 584 | 2013-03-22 19:01:40 | 
 | 302 | 584 | 2013-03-22 19:05:24 | 
 | 303 | 584 | 2013-03-22 19:13:18 | 
 | 304 | 590 | 2013-04-10 23:24:17 | 
 | 305 | 522 | 2013-04-11 18:44:07 | 

解决方案

尝试：

  SELECT  COUNT（*） FROM 表1 t1 
  LEFT   JOIN 表2 t2  ON  t1.id = t2.referred_by_user_id 
  WHERE  t2.referred_by_user_id  IS   NULL  

how to count all user id of the first table that don''t have refer id in the second table?

Table 1:
+-----+------------+---------------------+
| id  | first_name | created_at          |
+-----+------------+---------------------+
| 522 | dddd       | 2013-04-11 18:44:07 |
| 523 | cccc       | 2013-03-01 06:19:12 |
| 524 | ffff       | 2013-03-06 21:07:06 |
| 525 | rrrr       | 2013-03-06 22:05:08 |
| 590 | tttt       | 2013-03-16 16:39:59 |
| 584 | yyyy       | 2013-03-22 14:46:28 |
 
Table 2:
+-----+---------------------+---------------------+
| id  | referred_by_user_id | created_at          |
+-----+---------------------+---------------------+
| 298 |                 522 | 2013-02-22 22:19:06 |
| 299 |                 584 | 2013-03-22 18:27:49 |
| 300 |                 584 | 2013-03-22 18:48:23 |
| 301 |                 584 | 2013-03-22 19:01:40 |
| 302 |                 584 | 2013-03-22 19:05:24 |
| 303 |                 584 | 2013-03-22 19:13:18 |
| 304 |                 590 | 2013-04-10 23:24:17 |
| 305 |                 522 | 2013-04-11 18:44:07 |

解决方案

Try:

SELECT COUNT(*) FROM Table1 t1 
LEFT JOIN Table2 t2 ON t1.id=t2.referred_by_user_id 
WHERE t2.referred_by_user_id IS NULL

这篇关于从1表中计算id，其中id不在第二个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！