如何获取除Id列外其余列值均相同的行？SQL查询优化求助

The main issue with your current SQL is that you're not including strBedrijf in the list of columns you match between tables A and B. Since you want all columns except Id to be identical, this is a critical missing condition—without it, rows from different companies (different strBedrijf) that happen to share the same supplier, invoice number, date, and amount will be incorrectly flagged as duplicates.

Corrected Self-Join Approach #

Here's your original query updated to include the missing strBedrijf match, plus some cleanup to use explicit joins instead of the old comma syntax (which makes the logic clearer):

SELECT DISTINCT
    a.strBedrijf,
    a.IdLeverancier,
    a.strLevFactNr,
    a.Id,
    a.dtmFactuur,
    a.fBedragInc,
    be.bDeleted,
    'https://documents.blabla/' + (SELECT TOP(1) textval FROM tblsettings WHERE id='Workflow_customerid') + '/?p=PurchaseInvoiceDetails&Id=' + a.id AS Url,
    CASE WHEN be.bDeleted = 'JA' THEN 'NEE' WHEN be.bDeleted = 'NEE' THEN 'JA' END AS AdministratieActief 
FROM tblfacturen a
JOIN tblfacturen b 
    ON a.strBedrijf = b.strBedrijf
    AND a.IdLeverancier = b.IdLeverancier 
    AND a.strLevFactNr = b.strLevFactNr 
    AND a.dtmFactuur = b.dtmFactuur 
    AND a.fBedragInc = b.fBedragInc 
    AND a.ID != b.ID
JOIN tblBedrijven be 
    ON a.strBedrijf = be.Id 
WHERE be.bDeleted = 'NEE' 
  AND a.bDeleted = 'NEE' 
  AND b.bDeleted = 'NEE' 
  AND a.strlevfactnr NOT LIKE 'corr%'

I added DISTINCT because the self-join will return each duplicate row twice (once as A, once as B). This ensures you only get each unique duplicate row once.

Better Approach: Window Functions #

A cleaner and more efficient way to find duplicates is to use a window function to count how many rows share the same non-Id column values. This avoids the need for a self-join entirely:

WITH DuplicateGroups AS (
    SELECT
        a.strBedrijf,
        a.IdLeverancier,
        a.strLevFactNr,
        a.Id,
        a.dtmFactuur,
        a.fBedragInc,
        be.bDeleted,
        'https://documents.blabla/' + (SELECT TOP(1) textval FROM tblsettings WHERE id='Workflow_customerid') + '/?p=PurchaseInvoiceDetails&Id=' + a.id AS Url,
        CASE WHEN be.bDeleted = 'JA' THEN 'NEE' WHEN be.bDeleted = 'NEE' THEN 'JA' END AS AdministratieActief,
        -- Count how many rows match all non-Id columns
        COUNT(*) OVER (
            PARTITION BY 
                a.strBedrijf, 
                a.IdLeverancier, 
                a.strLevFactNr, 
                a.dtmFactuur, 
                a.fBedragInc,
                a.bDeleted -- Include this since you filter it to 'NEE'
        ) AS DuplicateCount
    FROM tblfacturen a
    JOIN tblBedrijven be 
        ON a.strBedrijf = be.Id
    WHERE be.bDeleted = 'NEE'
      AND a.bDeleted = 'NEE'
      AND a.strLevFactNr NOT LIKE 'corr%'
)
SELECT
    strBedrijf,
    IdLeverancier,
    strLevFactNr,
    Id,
    dtmFactuur,
    fBedragInc,
    bDeleted,
    AdministratieActief
FROM DuplicateGroups
WHERE DuplicateCount > 1 -- Only keep rows that have duplicates
ORDER BY strBedrijf, dtmFactuur, fBedragInc; -- Optional: Sort for readability

This query first creates a CTE where each row gets a count of how many rows share the same values in all the columns you care about (excluding Id). Then we filter to only keep rows where that count is greater than 1—those are your duplicate rows.

This approach is easier to read, avoids duplicate rows in the result set without needing DISTINCT, and is often faster for large datasets since it doesn't require joining the table to itself.

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