使用MINUS操作时考虑重复值及表唯一性的SQL实现需求

首先，咱们得抓住核心需求：只有当某个前缀（比如Kavana、Sandhya）在Table1中存在多条记录时，才对该前缀的记录执行MINUS操作（排除ExceptionTable里的条目）；如果前缀唯一（整个Table1里只有一条该前缀的记录），哪怕它在ExceptionTable里，也要保留下来。

下面是具体的SQL实现思路，以通用SQL语法为例（不同数据库的字符串分割函数可能有细微差异，按需调整即可）：

步骤1：统计Table1中每个前缀的出现次数 #

先提取每条记录的前缀，统计每个前缀对应的记录总数：

WITH prefix_counts AS (
    SELECT 
        SUBSTRING_INDEX(col, '-', 1) AS prefix,
        COUNT(*) AS record_count
    FROM Table1
    GROUP BY SUBSTRING_INDEX(col, '-', 1)
)

这里的col是Table1中存储Kavana-01这类值的列名，记得替换成你的实际列名。SUBSTRING_INDEX(col, '-', 1)的作用是提取连字符前面的前缀部分。

步骤2：关联表并筛选目标记录 #

把前缀统计结果和Table1、ExceptionTable关联，筛选出符合要求的记录：

SELECT t1.col
FROM Table1 t1
JOIN prefix_counts pc ON SUBSTRING_INDEX(t1.col, '-', 1) = pc.prefix
LEFT JOIN ExceptionTable et ON t1.col = et.col
WHERE 
    -- 前缀唯一的记录，直接保留
    pc.record_count = 1
    -- 前缀不唯一的记录，只保留不在ExceptionTable里的
    OR (pc.record_count > 1 AND et.col IS NULL)
ORDER BY t1.col;

逻辑验证 #

代入咱们的测试数据看看：

• 对于Kavana-01：前缀Kavana在Table1中只有1条记录，所以不管它在ExceptionTable里，都会被保留。
• 对于Sandhya-01：前缀Sandhya在Table1中有2条记录，且它存在于ExceptionTable中，所以会被排除。
• 对于Sandhya-02：前缀Sandhya有2条记录，且它不在ExceptionTable中，所以会被保留。

最终结果正好是Kavana-01、Sandhya-02，和Outputtable完全一致。

如果你的数据库不支持CTE（WITH子句），也可以用子查询的方式实现：

SELECT t1.col
FROM Table1 t1
JOIN (
    SELECT 
        SUBSTRING_INDEX(col, '-', 1) AS prefix,
        COUNT(*) AS record_count
    FROM Table1
    GROUP BY SUBSTRING_INDEX(col, '-', 1)
) pc ON SUBSTRING_INDEX(t1.col, '-', 1) = pc.prefix
LEFT JOIN ExceptionTable et ON t1.col = et.col
WHERE 
    pc.record_count = 1
    OR (pc.record_count > 1 AND et.col IS NULL)
ORDER BY t1.col;

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