当前位置: 动力学知识库 > 问答 > 编程问答 >

tsql - How to eliminate duplicate rows where a duplicate is defined by transposed values between 2 columns

问题描述:

I want to eliminate any row in which the value of Column A appears in Column B along with the value of Column B appearing in Column A.

For example

id | column_A | column_B

------------------------

1 | quick | brown

2 | quick | fox

3 | brown | quick

4 | lazy | dog

5 | fox | quick

I am trying to get a result set

id | column_A | column_B

------------------------

1 | quick | brown

2 | quick | fox

4 | lazy | dog

As you can see the rows with ids 3 & 5 are eliminate as, in the row with id = 3 the value of column_a = brown and column_b = quick which matches the transposed values of id = 1 where column_a = quick and column_b = brown. Similarly with the row with id = 2 eliminating the row where id = 5.

网友答案:
DECLARE @Tx TABLE (
     ID         INT IDENTITY
    ,column_A   NVARCHAR(20)
    ,column_B   NVARCHAR(20)
    )

INSERT INTO @Tx VALUES
     ('quick','brown')
    ,('quick','fox')
    ,('brown','quick')
    ,('lazy','dog')
    ,('fox','quick')

;WITH   RN
    AS (
        SELECT ID,
            CASE WHEN column_A < column_B THEN column_A + column_B
                 ELSE column_B + column_A END AS RNx
            FROM @Tx
        ),

        RO
    AS (
        SELECT ID, RNx, ROW_NUMBER() OVER (PARTITION BY RNx ORDER BY ID) AS RON
            FROM RN
        )

DELETE Tx
    FROM @Tx Tx
        LEFT JOIN RO
            ON Tx.ID = RO.ID AND RO.RON > 1
                WHERE RO.ID IS NOT NULL

SELECT * FROM @Tx
分享给朋友:
您可能感兴趣的文章:
随机阅读: