tabla - no repetir registros en consulta sql

Identificar filas duplicadas lógicamente en una tabla

Question

Aquí está mi tabla de entrada.

 ID | Player1 | Player2 | Player3
===================================
101 | Tom | Dick | Harry
102 | Jack | Martin | Anna
103 | Harry | Tom | Dick

Aquí vemos, para ID 101 y 103, los Jugadores son esencialmente iguales, aunque en diferente orden. Por lo tanto, deben tratarse como filas duplicadas.

¿Cómo identifico de alguna manera las filas duplicadas, escribiendo una consulta? Algo que devuelve que "ID 101 e ID 103 son duplicados" ??

Answer 1

Esto debería funcionar:

WITH Src AS
(
SELECT * FROM (VALUES
(101, ''Tom'', ''Dick'', ''Harry''),
(102, ''Jack'', ''Martin'', ''Anna''),
(103, ''Harry'', ''Tom'', ''Dick''),
(104, ''Dick'', ''Tom'', ''Harry''),
(105, ''Anna'', ''Martin'', ''Jack''),
(106, ''Anna'', ''Martin'', ''Joe'')
) T(ID, Player1, Player2, Player3)
), Numbered AS
(
SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) N
FROM
(
SELECT ID,
CONVERT(nvarchar(MAX), Player1) Player1,
CONVERT(nvarchar(MAX), Player2) Player2,
CONVERT(nvarchar(MAX), Player3) Player3
FROM Src
) T1
UNPIVOT
(Name FOR Player IN (Player1, Player2, Player3)) PV
), Grouped AS
(
SELECT N1.ID, N2.ID DuplicateID
FROM Numbered N1
CROSS JOIN Numbered N2
GROUP BY N1.ID,N2.ID
HAVING SUM(CASE WHEN N1.N=N2.N AND N1.ID!=N2.ID AND N1.Name=N2.Name THEN 1 END)=MAX(N1.N)
)
SELECT *
FROM Grouped
WHERE ID<DuplicateID

Answer 2

Necesitas que cada identificación encuentre un mínimo de 3 valores

player1+player2+player3
player2+player3+player1
player3+player1+player2

El seleccionar para obtener el valor mínimo

Select sub.id, min(sub.name)
FROM (
select
t.id,
CASE
WHEN rows.row=1 THEN t.player1+t.player2+t.player3
WHEN rows.row=2 THEN t.player2+t.player3+t.player1
WHEN rows.row=3 THEN t.player3+t.player1+t.player2
END as name
from t,
(select 1 as row
union
select 2 as row
union
select 3 as row) rows) sub
GROUP by sub.id

Entonces tienes una mesa como esta

ID concat_names
1 DickHarryTom
2 AnnaJackMartin
3 DickHarryTom

Y puedes comparar concat_names para encontrar duplicados

Answer 3

/*
CREATE TABLE myTest
(ID INT,
Player1 VARCHAR(50),
Player2 VARCHAR(50),
Player3 VARCHAR(50));
GO
INSERT INTO myTest
VALUES
(101, ''Tom'', ''Dick'', ''Harry''),
(102, ''Jack'', ''Martin'', ''Anna''),
(103, ''Harry'', ''Tom'', ''Dick'');
GO
*/
WITH
Players AS (
SELECT
ID,
Player1 AS Player
FROM
myTest
UNION ALL
SELECT
ID,
Player2 AS Player
FROM
myTest
UNION ALL
SELECT
ID,
Player3 AS Player
FROM
myTest),
PlayersSorted AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Player) AS RowNo
FROM
Players)
SELECT DISTINCT
a.ID
FROM
PlayersSorted a
JOIN
PlayersSorted b
ON a.ID <> b.ID
AND a.Player = b.Player
AND a.RowNo = b.RowNo;