优化MySQL查询：添加重复证书计数并筛选重复项

原始查询：

SELECT NrCertificadoPesagem, CodArquivoUsina, Safra, Arquivo 
FROM t413 
WHERE NrCertificadoPesagem <> "00000000" 
GROUP BY NrCertificadoPesagem, CodArquivoUsina, Safra, Arquivo 
ORDER BY NrCertificadoPesagem ASC, CodArquivoUsina ASC, Safra ASC;

需求说明 #

• 添加CertificadosQty列，统计每个NrCertificadoPesagem的重复次数
• 仅保留CertificadosQty > 1的记录

方案1：使用窗口函数（MySQL 8.0+ 推荐） #

窗口函数可直接在原查询基础上计算分组计数，无需多层嵌套，性能更优：

SELECT 
    NrCertificadoPesagem,
    CodArquivoUsina,
    Safra,
    Arquivo,
    CertificadosQty
FROM (
    SELECT 
        NrCertificadoPesagem,
        CodArquivoUsina,
        Safra,
        Arquivo,
        COUNT(*) OVER (PARTITION BY NrCertificadoPesagem) AS CertificadosQty
    FROM t413
    WHERE NrCertificadoPesagem <> "00000000"
    GROUP BY NrCertificadoPesagem, CodArquivoUsina, Safra, Arquivo
) AS sub
WHERE CertificadosQty > 1
ORDER BY NrCertificadoPesagem ASC, CodArquivoUsina ASC, Safra ASC;

• 内层查询用COUNT(*) OVER (PARTITION BY NrCertificadoPesagem)计算每个证书的总重复数
• 外层直接过滤计数≤1的记录，逻辑清晰，执行效率高

方案2：子查询+JOIN（兼容MySQL 5.x版本） #

若你的MySQL版本不支持窗口函数，可先统计证书重复数，再与原查询结果关联：

SELECT 
    t.NrCertificadoPesagem,
    t.CodArquivoUsina,
    t.Safra,
    t.Arquivo,
    cnt.CertificadosQty
FROM (
    SELECT NrCertificadoPesagem, CodArquivoUsina, Safra, Arquivo 
    FROM t413 
    WHERE NrCertificadoPesagem <> "00000000" 
    GROUP BY NrCertificadoPesagem, CodArquivoUsina, Safra, Arquivo
) AS t
JOIN (
    SELECT NrCertificadoPesagem, COUNT(*) AS CertificadosQty
    FROM t413
    WHERE NrCertificadoPesagem <> "00000000"
    GROUP BY NrCertificadoPesagem
    HAVING CertificadosQty > 1
) AS cnt ON t.NrCertificadoPesagem = cnt.NrCertificadoPesagem
ORDER BY t.NrCertificadoPesagem ASC, t.CodArquivoUsina ASC, t.Safra ASC;

• 子查询cnt先筛选出重复次数>1的证书及其计数
• 再将原查询结果与cnt关联，直接得到符合条件的记录，避免冗余嵌套

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