如何正确获取TableA中未被TableB充分匹配的记录（含重复项）

Got it, let's break down what you need here. Your original query only catches ArticleIDs that have no matches at all in TableB (like 3), but you want to also include IDs where TableA has more occurrences than TableB (like 2, which shows up 3 times in A vs 2 in B, and 5 which is 2 vs 1).

核心思路 #

First, we need to calculate the occurrence count of each ArticleID in both tables, then compare those counts to find where TableA's count is higher.

完整查询语句（用CTE，可读性更高） #

WITH A_Counts AS (
    -- 统计TableA中每个ArticleID的出现次数
    SELECT ArticleID, COUNT(*) AS CountA
    FROM TableA
    GROUP BY ArticleID
),
B_Counts AS (
    -- 统计TableB中每个ArticleID的出现次数
    SELECT ArticleID, COUNT(*) AS CountB
    FROM TableB
    GROUP BY ArticleID
)
-- 对比两个统计结果，筛选TableA次数更多的ID
SELECT a.ArticleID
FROM A_Counts a
LEFT JOIN B_Counts b ON a.ArticleID = b.ArticleID
-- 用COALESCE处理TableB中没有的ID（把NULL转为0）
WHERE a.CountA > COALESCE(b.CountB, 0);

不用CTE的写法（兼容旧版SQL） #

如果你的数据库不支持CTE，也可以用子查询实现：

SELECT a.ArticleID
FROM (
    SELECT ArticleID, COUNT(*) AS CountA 
    FROM TableA 
    GROUP BY ArticleID
) a
LEFT JOIN (
    SELECT ArticleID, COUNT(*) AS CountB 
    FROM TableB 
    GROUP BY ArticleID
) b ON a.ArticleID = b.ArticleID
WHERE a.CountA > COALESCE(b.CountB, 0);

为什么你的原查询不行？ #

Your original LEFT JOIN + WHERE B.ArticleID IS NULL only returns rows where no matching row exists in TableB at all. It doesn't account for how many times each ID appears—so even if TableA has 3 copies of ID 2 and TableB has 2, those rows still get matched and excluded from the result.

Running either of the queries above will give you exactly the expected result: ArticleID: 2, 3, 5.

内容的提问来源于stack exchange，提问作者maxu