sql - 不存在表记录

Question

减去表 seq 和文章。seq 表有 4 条 seq '3' 的匹配记录，而 article 表有 3 条 seqid '3' 的匹配记录。我想从 seq 表中减去文章表（4-3 = 1）。输出仅返回一条记录。

SEQ 
id  seq
9    3
11   3
12   4
13   3
16   4
19   3 

article 
id  seqid  article
 6    3    HR Skills
 7    6    Finance 
 9    4    IT
12    3    HR Skills
15    3    HR Skills

SELECT s.id, s.seq, a.article 
FROM seq s 
INNER JOIN article a ON s.seq = a.seqid 
WHERE s.seq NOT EXISTS (select seqid from article);

当我执行查询时，它会返回两个表中的所有匹配记录。我希望查询减去（4-3 = 1）。

输出：（4-3 =1）

s.id s.seq a.seqid   a.article
 13    3      3      HR Skills

标签： sql

Answer 1

查询：

 select seqcnt.cnt ,articlecnt.cnt,  isnull(seqcnt.cnt,0) - isnull(articlecnt.cnt,0) subtract  , article. article
    from 
    (select distinct seq , count(seq) cnt from seq  group by seq ) seqcnt
    left join (select distinct seqid , count(seqid) cnt from  article   group by seqid ) articlecnt on seqcnt.seq =  articlecnt.seqid 
    left join (select distinct seqid , article  from  article) article on article. seqid = seqcnt.seq

输出