MySQL如何在两张表均无唯一键时排除对应数量的匹配行

适用MySQL 8.0+（支持窗口函数） #

核心思路是用窗口函数给待办表的同类型、同名条目编排序号，再关联已完成表的对应条目计数，仅保留序号超过已完成计数的条目。

查询过滤后的待办列表 #

WITH stack_rn AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY type, name ORDER BY stack_id) AS rn
    FROM stack
),
temp_count AS (
    SELECT type, name, COUNT(*) AS cnt
    FROM temp
    GROUP BY type, name
)
SELECT s.stack_id, s.type, s.name
FROM stack_rn s
LEFT JOIN temp_count t ON s.type = t.type AND s.name = t.name
WHERE t.cnt IS NULL OR s.rn > t.cnt;

执行后将直接返回你需要的待办结果。

直接删除待办表中已完成的对应条目 #

如果不需要保留原待办表数据，可直接执行删除语句：

DELETE s FROM stack s
JOIN (
    SELECT stack_id,
           ROW_NUMBER() OVER (PARTITION BY type, name ORDER BY stack_id) AS rn
    FROM stack
) s_rn ON s.stack_id = s_rn.stack_id
JOIN (
    SELECT type, name, COUNT(*) AS cnt
    FROM temp
    GROUP BY type, name
) t ON s.type = t.type AND s.name = t.name
WHERE s_rn.rn <= t.cnt;

适用MySQL 5.x（不支持窗口函数） #

通过用户变量实现行号逻辑，效果和窗口函数一致：

查询过滤后的待办列表 #

SELECT s.stack_id, s.type, s.name
FROM (
    SELECT *,
           @rn := IF(@pre_type = type AND @pre_name = name, @rn + 1, 1) AS rn,
           @pre_type := type,
           @pre_name := name
    FROM stack, (SELECT @rn := 0, @pre_type := '', @pre_name := '') AS init
    ORDER BY type, name, stack_id
) s
LEFT JOIN (
    SELECT type, name, COUNT(*) AS cnt
    FROM temp
    GROUP BY type, name
) t ON s.type = t.type AND s.name = t.name
WHERE t.cnt IS NULL OR s.rn > t.cnt;

直接删除待办表中已完成的对应条目 #

DELETE s FROM stack s
JOIN (
    SELECT stack_id,
           @rn := IF(@pre_type = type AND @pre_name = name, @rn + 1, 1) AS rn,
           @pre_type := type,
           @pre_name := name
    FROM stack, (SELECT @rn := 0, @pre_type := '', @pre_name := '') AS init
    ORDER BY type, name, stack_id
) s_rn ON s.stack_id = s_rn.stack_id
JOIN (
    SELECT type, name, COUNT(*) AS cnt
    FROM temp
    GROUP BY type, name
) t ON s.type = t.type AND s.name = t.name
WHERE s_rn.rn <= t.cnt;

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