获得数据基本重复的

Question

一个数据库有
id num1 num2 num3 num4 num5 num6
1   1    2    3   4     5   6
2   2    3    4   7     8   11
3   4    5    6   80    90   92
4   2    6    16   10    12   22
5   6    10   12  22    94    95我想获取重复的数据出来,
重复的条件,是,num1~num6中,重复出现3个(包括3)个以上的数据
如ID=1的和ID=2的,有3个整数是重复出现的(2,3,4)
ID=4和ID=5的也有(6,10,12,22)是重复的.

Answer 1

Hi,CREATE TABLE #tbl_Test
(id int,
num1 int,
num2 int,
num3 int,
num4 int,
num5 int,
num6 int)INSERT INTO #tbl_Test
SELECT 1,1,2,3,4,5,6
UNION ALL
SELECT 2,2,3,4,7,8,11
UNION ALL
SELECT 3,4,5,6,80,90,92
UNION ALL
SELECT 4,2,6,16,10,12,22
UNION ALL
SELECT 5,6,10,12,22,94,95--选择重复的记录超过3个的num
SELECT num
FROM 
(
SELECT num,row = ROW_NUMBER()OVER(ORDER BY num) FROM
(
SELECT A.num,A.id,RANK = RANK()OVER(PARTITION BY A.num ORDER BY A.id) FROM
(
SELECT num = num1,id = id  FROM #tbl_Test 
UNION ALL
SELECT num = num2 ,id = id FROM #tbl_Test 
UNION ALL
SELECT num = num3,id = id FROM #tbl_Test 
UNION ALL
SELECT num = num4,id = id FROM #tbl_Test 
UNION ALL
SELECT num = num5,id = id FROM #tbl_Test 
UNION ALL
SELECT num = num6,id = id FROM #tbl_Test 
)A
WHERE A.id IN (4,5)
)B
WHERE B.RANK >= 2 
)CWHERE (SELECT COUNT(num)
FROM 
(
SELECT num,row = ROW_NUMBER()OVER(ORDER BY num) FROM
(
SELECT A.num,A.id,RANK = RANK()OVER(PARTITION BY A.num ORDER BY A.id) FROM
(
SELECT num = num1,id = id  FROM #tbl_Test 
UNION ALL
SELECT num = num2 ,id = id FROM #tbl_Test 
UNION ALL
SELECT num = num3,id = id FROM #tbl_Test 
UNION ALL
SELECT num = num4,id = id FROM #tbl_Test 
UNION ALL
SELECT num = num5,id = id FROM #tbl_Test 
UNION ALL
SELECT num = num6,id = id FROM #tbl_Test 
)A
WHERE A.id IN (4,5)
)B
WHERE B.RANK >= 2 
)C) > = 3-- Result
num
6
10
12
22

Answer 2

IF OBJECT_ID('TB') IS NOT NULL
  DROP TABLE TB
GOCREATE TABLE TB
(ID INT, NUM1 INT, NUM2 INT, NUM3 INT, NUM4 INT, NUM5 INT, NUM6 INT)INSERT INTO TB
SELECT 1,1,2,3,4,5,6 UNION ALL
SELECT 2,2,3,4,7,8,11 UNION ALL
SELECT 3,4,5,6,80,90,92 UNION ALL
SELECT 4,2,6,16,10,12,22 UNION ALL
SELECT 5,6,10,12,22,94,95 ;WITH TA AS
( SELECT ID, NUM = NUM1  FROM TB UNION ALL
  SELECT ID, NUM2        FROM TB UNION ALL
  SELECT ID, NUM3        FROM TB UNION ALL
  SELECT ID, NUM4        FROM TB UNION ALL
  SELECT ID, NUM5        FROM TB UNION ALL
  SELECT ID, NUM6        FROM TB)
SELECT A.ID, B.ID
FROM TA A
JOIN TA B
ON A.ID < B.ID AND A.NUM = B.NUM
GROUP BY A.ID, B.ID
HAVING COUNT(*) >= 3DROP TABLE TB