mysql - mysql 内连接后如何获取剩余行

Question

我有两张 table

1.申请人表(id,姓名)

2.posts表(id,post_name,applicant_id)

如果我进行内部联接，我将获得有帖子的申请人( SELECT COUNT(*) FROM applicants INNER JOIN posts ON posts.applicant_id = 申请人.id ).

现在我需要做些什么来吸引没有任何职位的申请人。

Answer 1

您需要一个外部联接和HAVING子句

SELECT
      a.id
    , COUNT(p.applicant_id) post_count
FROM applicants a
      LEFT OUTER JOIN posts p
                  ON a.id = p.applicant_id
GROUP BY
      a.id
HAVING COUNT(p.applicant_id) = 0

实际上你不需要分组依据，这就足够了:

SELECT
      a.id
FROM applicants a
      LEFT OUTER JOIN posts p
                  ON a.id = p.applicant_id
WHERE p.applicant_id IS NULL