在不同列的同一行中重复 [英] Duplicate in same row in different columns

问题描述

我有一张这样的桌子:

CREATE TABLE #my_table (
  intID int IDENTITY (1, 1),
  num_1 varchar(100) NOT NULL,
  num_2 varchar(100) NOT NULL,
  num_3 varchar(100) NOT NULL,
  num_4 varchar(100),
  num_5 varchar(100),
  isDuplicate char(1) DEFAULT 'N'
)

INSERT INTO #my_table (num_1, num_2, num_3, num_4, num_5)
  VALUES ('a', 'b', 'c', 'd', 'e')
INSERT INTO #my_table (num_1, num_2, num_3, num_4, num_5)
  VALUES ('a', 'b', 'c', 'd', 'e')
INSERT INTO #my_table (num_1, num_2, num_3, num_4, num_5)
  VALUES ('a', 'b', 'c', 'd', 'e')
INSERT INTO #my_table (num_1, num_2, num_3, num_4, num_5)
  VALUES ('a', 'b', 'a', 'd', 'e')
INSERT INTO #my_table (num_1, num_2, num_3, num_4, num_5)
  VALUES ('a', 'b', 'a', 'd', 'e')
INSERT INTO #my_table (num_1, num_2, num_3, num_4, num_5)
  VALUES ('a', 'b', 'c', 'd', 'c')

我需要在列中查找重复项并获取重复的行号.

I need to find duplicates in columns and get the row number which is duplicate.

我的结果应该是重复行最后 3 行，重复标志应更新为Y"

my result should be duplicate rows last 3 rows and is duplicate flag should be updated to 'Y'

推荐答案

这也可以解决问题:

UPDATE #my_table
    SET isDuplicate = 'Y'
WHERE intID IN (
    SELECT intID FROM #my_table
    WHERE EXISTS 
       (SELECT 1 
        FROM (VALUES 
                   (num_1)
                  ,(num_2)
                  ,(num_3)
                  ,(num_4)
                  ,(num_5)) AS X (n)
        WHERE NULLIF(n, '') IS NOT NULL
        GROUP BY n
    HAVING COUNT(*)>1
   )
)

有关表值构造函数的更多信息，您可以找到此处.

More information about table value constructors you can find here.

这篇关于在不同列的同一行中重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！