Cómo encontrar valores duplicados en una tabla de SQL Server

Resumen: en este tutorial, aprenderás a usar la cláusula GROUP BY o la función ROW_NUMBER() para encontrar valores duplicados en una tabla de SQL Server.
Introducción para encontrar los valores duplicados
Técnicamente, utiliza las restricciones UNIQUE
para imponer la unicidad de las filas en una o más columnas de una tabla. Sin embargo, a veces puedes encontrar valores duplicados en una tabla debido al diseño deficiente de la base de datos, errores de la aplicación o datos sin limpiar de fuentes externas. En este tutorial veremos cómo identificar estos valores duplicados de manera efectiva.
Para encontrar los valores duplicados en una tabla, sigue estos pasos:
- Primero, define los criterios para los valores duplicados: valores en una sola columna o varias columnas.
- En segundo lugar, escribe una consulta para buscar duplicados.
Si también deseas eliminar las filas duplicadas, puede ir al tutorial de eliminación de duplicados de una tabla.
Configuración de una tabla de muestra
Primero, crea una nueva tabla llamada t1
que contenga tres columnas id
, a
y b
.
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id INT IDENTITY(1, 1),
a INT,
b INT,
PRIMARY KEY(id)
);
Luego, inserte algunas filas en la tabla t1
:
INSERT INTO
t1(a,b)
VALUES
(1,1),
(1,2),
(1,3),
(2,1),
(1,2),
(1,3),
(2,1),
(2,2);
La tabla t1
contiene las siguientes filas duplicadas:
(1,2)
(2,1)
(1,3)
Ahora el objetivo es escribir una consulta para encontrar las filas duplicadas anteriores.
Usando la cláusula GROUP BY para encontrar valores duplicados en una tabla
Esta declaración usa la cláusula GROUP BY, HAVING y COUNT para encontrar las filas duplicadas en las columnas a
y b
de la tabla t1
:
SELECT
a,
b,
COUNT(*) occurrences
FROM t1
GROUP BY
a,
b
HAVING
COUNT(*) > 1;
Resultado:
a | b | occurrences |
---|---|---|
2 | 1 | 2 |
1 | 2 | 2 |
1 | 3 | 2 |
Cómo funciona:
- Primero, la cláusula GROUP BY agrupa las filas en grupos por valores iguales en las columnas
a
yb
. - En segundo lugar, la función COUNT() devuelve el número de ocurrencias de cada grupo (
a,b
). - Tercero, la cláusula HAVING mantiene solo grupos duplicados, que son grupos que tienen más de una aparición u ocurrencias.
Para devolver la fila completa por cada fila duplicada, une el resultado de la consulta anterior con la tabla t1
usando una expresión de tabla común (CTE
):
WITH cte AS (
SELECT
a,
b,
COUNT(*) occurrences
FROM t1
GROUP BY
a,
b
HAVING
COUNT(*) > 1
)
SELECT
t1.id,
t1.a,
t1.b
FROM t1
INNER JOIN cte ON
cte.a = t1.a AND
cte.b = t1.b
ORDER BY
t1.a,
t1.b;
Resultado:
id | a | b |
---|---|---|
2 | 1 | 2 |
5 | 1 | 2 |
6 | 1 | 3 |
3 | 1 | 3 |
4 | 2 | 1 |
7 | 2 | 1 |
Generalmente, la consulta para encontrar los valores duplicados en una columna usando la cláusula GROUP BY es la siguiente:
SELECT
col,
COUNT(col)
FROM
table_name
GROUP BY
col
HAVING
COUNT(col) > 1;
La consulta para encontrar los valores duplicados en varias columnas usando la cláusula GROUP BY:
SELECT
col1,col2,...
COUNT(*)
FROM
table_name
GROUP BY
col1,col2,...
HAVING
COUNT(*) > 1;
Usando la función ROW_NUMBER() para encontrar duplicados en una tabla
La siguiente declaración usa la función ROW_NUMBER() para encontrar filas duplicadas basadas en las columnas a y b:
WITH cte AS (
SELECT
a,
b,
ROW_NUMBER() OVER (
PARTITION BY a,b
ORDER BY a,b) rownum
FROM
t1
)
SELECT
*
FROM
cte
WHERE
rownum > 1;
Resultado:
a | b | rownum |
---|---|---|
1 | 2 | 2 |
1 | 3 | 2 |
2 | 1 | 2 |
Cómo funciona:
Primero, ROW_NUMBER() distribuye filas de la tabla t1
en particiones por valores en las columnas a
y b
. Las filas duplicadas tendrán valores repetidos en las columnas a
y b
, pero diferentes números de fila, como se muestra en la siguiente tabla:
a | b | rownum |
---|---|---|
1 | 1 | 1 |
1 | 2 | 1 |
1 | 2 | 2 |
1 | 3 | 1 |
1 | 3 | 2 |
2 | 1 | 1 |
2 | 1 | 2 |
2 | 2 | 1 |
En segundo lugar, la consulta externa elimina la primera fila de cada grupo.
Generalmente, esta declaración usa la función ROW_NUMBER()
para encontrar los valores duplicados en una columna de una tabla:
WITH cte AS (
SELECT
col,
ROW_NUMBER() OVER (
PARTITION BY col
ORDER BY col) row_num
FROM
t1
)
SELECT * FROM cte
WHERE row_num > 1;
En este tutorial, aprendiste a usar la cláusula GROUP BY o la función ROW_NUMBER() para encontrar valores duplicados en SQL Server.