如何高效对比含可选缺失多键的两个Excel表并提取非共有记录

一、SQL实现方案 #

你之前的SQL逻辑错误是核心问题：用OR连接字段匹配规则、嵌套逻辑混乱，完全不符合业务匹配要求，所以没有返回结果。

首先明确匹配规则：两条记录判定为匹配当且仅当：

1. 交易金额AMOUNT完全相等
2. 两条记录所有非0的ACCOUNT/TC/AUTO字段，都和对方的对应字段值相等（适配双向字段缺失场景）

正确的查询语句如下，直接用EXISTS子查询过滤即可，不需要复杂嵌套：

-- 查询文件1独有的记录
SELECT '文件1独有' AS 来源, ACCOUNT, TC, AUTO, AMOUNT
FROM excel1 e1
WHERE NOT EXISTS (
    SELECT 1 FROM excel2 e2
    WHERE e1.AMOUNT = e2.AMOUNT
    -- 校验e1非0字段匹配e2
    AND (e1.ACCOUNT = 0 OR e1.ACCOUNT = e2.ACCOUNT)
    AND (e1.TC = 0 OR e1.TC = e2.TC)
    AND (e1.AUTO = 0 OR e1.AUTO = e2.AUTO)
    -- 校验e2非0字段匹配e1
    AND (e2.ACCOUNT = 0 OR e2.ACCOUNT = e1.ACCOUNT)
    AND (e2.TC = 0 OR e2.TC = e1.TC)
    AND (e2.AUTO = 0 OR e2.AUTO = e1.AUTO)
)
UNION ALL
-- 查询文件2独有的记录
SELECT '文件2独有' AS 来源, ACCOUNT, TC, AUTO, AMOUNT
FROM excel2 e2
WHERE NOT EXISTS (
    SELECT 1 FROM excel1 e1
    WHERE e1.AMOUNT = e2.AMOUNT
    AND (e1.ACCOUNT = 0 OR e1.ACCOUNT = e2.ACCOUNT)
    AND (e1.TC = 0 OR e1.TC = e2.TC)
    AND (e1.AUTO = 0 OR e1.AUTO = e2.AUTO)
    AND (e2.ACCOUNT = 0 OR e2.ACCOUNT = e1.ACCOUNT)
    AND (e2.TC = 0 OR e2.TC = e1.TC)
    AND (e2.AUTO = 0 OR e2.AUTO = e1.AUTO)
)

如果不想部署正式数据库，可以用H2这类内存数据库，把两个Excel的数据导入内存表后执行上述SQL，执行完成直接销毁，没有持久化开销，效率远高于导入普通临时库。

二、Java内存实现方案 #

不需要找第三方的多键可选Map实现，按必须相等的AMOUNT字段分组，再加自定义匹配逻辑的方案，实现简单且性能极高，时间复杂度为O(n+m)（n、m为两个文件的记录数）。

实现步骤 #

1. 定义交易记录实体类，内置匹配逻辑

class TradeRecord {
    private String account;
    private String tc;
    private String auto;
    private String amount;

    // 构造方法、getter方法自行补充
    public boolean isMatched(TradeRecord other) {
        // 金额不等直接不匹配
        if (!this.amount.equals(other.amount)) {
            return false;
        }
        // 校验本对象所有非0字段匹配对方
        if (!"0".equals(this.account) && !this.account.equals(other.account)) return false;
        if (!"0".equals(this.tc) && !this.tc.equals(other.tc)) return false;
        if (!"0".equals(this.auto) && !this.auto.equals(other.auto)) return false;
        // 校验对方所有非0字段匹配本对象
        if (!"0".equals(other.account) && !other.account.equals(this.account)) return false;
        if (!"0".equals(other.tc) && !other.tc.equals(this.tc)) return false;
        if (!"0".equals(other.auto) && !other.auto.equals(this.auto)) return false;
        return true;
    }
}

2. 读取两个Excel的所有记录，分别存入List<TradeRecord> list1、List<TradeRecord> list2
3. 把其中一个列表按金额分组，减少匹配时的遍历范围

// 把文件2的记录按金额分组
Map<String, List<TradeRecord>> amountGroup = list2.stream()
        .collect(Collectors.groupingBy(TradeRecord::getAmount));

4. 遍历list1，筛选出文件1独有的记录

List<TradeRecord> onlyInList1 = list1.stream()
        .filter(record -> {
            List<TradeRecord> candidates = amountGroup.get(record.getAmount());
            // 对应金额没有候选记录，直接判定为独有
            if (candidates == null || candidates.isEmpty()) return true;
            // 遍历候选记录，没有匹配的则为独有
            return candidates.stream().noneMatch(record::isMatched);
        }).collect(Collectors.toList());

5. 反过来把list1按金额分组，遍历list2筛选出文件2独有的记录，合并两个独有集合就是最终结果。

如果记录量超过百万级，可以再优化分组键，比如把非0的ACCOUNT、TC也加入分组键的维度，进一步缩小候选匹配范围。

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