如果我理解你再次要求的话,我认为这实现了你想要的东西。

它至少会产生具体结果。

declare @Data table
(
    ID int not null,
    CODE varchar(3) not null,
    NUMBER1 varchar(3) not null,
    NUMBER2 varchar(3) not null,
    STEP int not null
)

insert into @Data values
    (1,  XXA ,  001 ,  009 , 1),
    (2,  XXB ,  001 ,  008 , 2),
    (3,  XXC ,  002 ,  009 , 3),
    (4,  XXA ,  002 ,  008 , 4)

-- this query returns the record with the highest STEP
-- excluding any other records that have the same CODE,
-- NUMBER1, or NUMBER2 and a lower STEP
select
    d.ID,
    d.CODE,
    d.NUMBER1,
    d.NUMBER2,
    d.STEP
from @Data d
where
    -- there does not exist any other record that has
    -- the same CODE, NUMBER1, or NUMBER2 and a higher STEP
    not exists (
        select 1
        from @Data duplicate
        where
            (
                duplicate.CODE = d.CODE
                or duplicate.NUMBER1 = d.NUMBER1
                or duplicate.NUMBER2 = d.NUMBER2
            )
            and (
                duplicate.STEP > d.STEP
                or (
                    duplicate.STEP = d.STEP
                    and duplicate.ID > d.ID
                )
            )
    )


-- this query returns the duplicate records with a lower STEP
select
    d.ID,
    d.CODE,
    d.NUMBER1,
    d.NUMBER2,
    d.STEP,
    HigherStep.ID as HigherStepID
from @Data d
inner join (
    -- join to the duplicate record that has a higher STEP
    select
        ID,
        CODE,
        NUMBER1,
        NUMBER2
    from @Data HighestStep
    where
        -- there does not exist any other record that has
        -- the same CODE, NUMBER1, or NUMBER2 and a higher STEP
        not exists (
            select 1
            from @Data duplicate
            where
                (
                    duplicate.CODE = HighestStep.CODE
                    or duplicate.NUMBER1 = HighestStep.NUMBER1
                    or duplicate.NUMBER2 = HighestStep.NUMBER2
                )
                and (
                    duplicate.STEP > HighestStep.STEP
                    or (
                        duplicate.STEP = HighestStep.STEP
                        and duplicate.ID > HighestStep.ID
                    )
                )
        )
) HigherStep on
    HigherStep.ID <> d.ID -- don t match this record to itself
    and (
        HigherStep.CODE = d.CODE
        or HigherStep.NUMBER1 = d.NUMBER1
        or HigherStep.NUMBER2 = d.NUMBER2
    )

下面是根据Yussan的评论,以较低的步骤重新检索记录的问题。 询问的一个问题是,它将无法记录那些并非立即重复记录的记录,而且进展最快。

-- this query returns the duplicate records with a lower STEP
select
    d.ID,
    d.CODE,
    d.NUMBER1,
    d.NUMBER2,
    d.STEP,
    HigherStep.ID as HigherStepID
from @Data d
inner join (
    -- join to the duplicate record that has a higher STEP
    select
        ID,
        CODE,
        NUMBER1,
        NUMBER2,
        STEP
    from @Data
) HigherStep on
    (
        HigherStep.CODE = d.CODE
        or HigherStep.NUMBER1 = d.NUMBER1
        or HigherStep.NUMBER2 = d.NUMBER2
    )
    and (
        HigherStep.STEP > d.STEP
        or (
            HigherStep.STEP = d.STEP
            and HigherStep.ID > d.ID
        )
    )

我希望我理解正确:

DECLARE @T1 TABLE
(
    ID INT,
    CODE VARCHAR(50),
    NUMBER1 VARCHAR(50),
    NUMBER2 VARCHAR(50),
    STEP INT
)

INSERT INTO @T1(ID, CODE, NUMBER1, NUMBER2, STEP)
VALUES(1,  XXA ,  001 ,  009 , 1)
INSERT INTO @T1(ID, CODE, NUMBER1, NUMBER2, STEP)
VALUES(2,  XXB ,  001 ,  008 , 2)
INSERT INTO @T1(ID, CODE, NUMBER1, NUMBER2, STEP)
VALUES(3,  XXC ,  002 ,  009 , 3)
INSERT INTO @T1(ID, CODE, NUMBER1, NUMBER2, STEP)
VALUES(4,  XXA ,  002 ,  008 , 4)

SELECT      TAB.ID,
            TAB.CODE,
            TAB.NUMBER1,
            TAB.NUMBER2,
            TAB.STEP,
            COALESCE(CAST(DUP_CODE_T.ID AS VARCHAR(50)) +  , ,   ) + 
            COALESCE(CAST(DUP_NUM1_T.ID AS VARCHAR(50)) +  , ,   ) + 
            COALESCE(CAST(DUP_NUM2_T.ID AS VARCHAR(50)),   ) AS DUPLICATE_WITH
FROM        @T1 AS TAB OUTER APPLY
            (
                SELECT      TOP 1 DUP_CODE.ID
                FROM        @T1 AS DUP_CODE
                WHERE       DUP_CODE.CODE = TAB.CODE AND
                            DUP_CODE.ID <> TAB.ID
            ) AS DUP_CODE_T OUTER APPLY
            (
                SELECT      TOP 1 DUP_NUM1.ID
                FROM        @T1 AS DUP_NUM1
                WHERE       DUP_NUM1.NUMBER1 = TAB.NUMBER1 AND
                            DUP_NUM1.ID <> TAB.ID
            ) AS DUP_NUM1_T OUTER APPLY
            (
                SELECT      TOP 1 DUP_NUM2.ID
                FROM        @T1 AS DUP_NUM2
                WHERE       DUP_NUM2.NUMBER2 = TAB.NUMBER2 AND
                            DUP_NUM2.ID <> TAB.ID
            ) AS DUP_NUM2_T

您可以加上WHERE条款或TOP 1来过滤结果。