能否在SQL窗口函数中用通配符识别带后缀的重复发票？

核心思路 #

PARTITION BY不支持直接使用通配符，正确做法是先提取发票编号的真实前缀（剥离错误后缀），再按「供应商+真实前缀」分组统计发票数量，数量大于1的即为存在错误发票的真实发票。

具体实现代码 #

1. 提取真实前缀并统计重复数

SELECT
    vendor,
    invoice_number,
    -- 提取真实发票前缀，正则规则可根据实际发票格式调整
    REGEXP_REPLACE(invoice_number, '^([A-Za-z]+[0-9]+).*$', '\1') AS real_invoice_prefix,
    -- 按供应商+真实前缀分组，统计同组发票总数
    COUNT(*) OVER (PARTITION BY vendor, REGEXP_REPLACE(invoice_number, '^([A-Za-z]+[0-9]+).*$', '\1')) AS duplicate_count
FROM invoices_table

2. 筛选存在错误发票的真实发票

若仅需要输出无后缀的真实发票，可在外层查询中过滤：

SELECT *
FROM (
    SELECT
        vendor,
        invoice_number,
        REGEXP_REPLACE(invoice_number, '^([A-Za-z]+[0-9]+).*$', '\1') AS real_invoice_prefix,
        COUNT(*) OVER (PARTITION BY vendor, REGEXP_REPLACE(invoice_number, '^([A-Za-z]+[0-9]+).*$', '\1')) AS duplicate_count
    FROM invoices_table
)
WHERE duplicate_count > 1
  AND invoice_number = real_invoice_prefix; -- 仅保留无后缀的原始真实发票

正则表达式适配说明 #

根据实际发票编号规则调整正则：

• 若真实发票为纯数字+后缀（如1234、1234-1），正则改为'^([0-9]+).*$'
• 若后缀仅为**-加数字**（如ABC001-1），可简化为REGEXP_REPLACE(invoice_number, '-[0-9]+$', '')
• 若后缀为末尾字母（如ABC003A），可使用REGEXP_REPLACE(invoice_number, '[A-Za-z]$', '')

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