Ejemplo
Considere las siguientes tablas (creadas con este SQL):
SELECT * FROM people
SELECT * FROM phones
Si escribimos esta consulta, obtenemos 2 resultados de Ned Flanders. Si este fuera un informe, nuestros usuarios pueden quejarse de que Ned Flanders está mostrando dos veces después de añadir el teléfono celular de columna.
SELECT
people.id,
people.first_name,
people.last_name,
phones.phone_number as 'cell'
FROM people
LEFT OUTER JOIN phones
ON phones.person_id=people.id
AND phones.phone_number_type='cell'
ORDER BY people.id
Identificar el problema
En nuestro ejemplo, podemos distinguir fácilmente el registro duplicado porque hay tan pocos registros. Pero en un gran conjunto de datos, no seremos capaces de detectar rápidamente los duplicados. La manera más fácil de identificar la presencia de resultados duplicados es comparar un recuento de los id de registros a un recuento distinto de los id de registros:
SELECT COUNT(id) AS cnt, COUNT(DISTINCT(id)) AS distinct_cnt
FROM (
SELECT
people.id,
people.first_name,
people.last_name,
phones.phone_number as 'cell'
FROM people
LEFT OUTER JOIN phones
ON phones.person_id=people.id
AND phones.phone_number_type='cell'
) results
En este ejemplo, la consulta anterior es poner en una subconsulta. Esto nos ahorra tener que refactorizar la consulta anterior demasiado con el fin de simplemente obtener un recuento de registros. La cláusula ORDER BY tuvo que ser retirado porque SQL Server no permite que sea utilizado en subconsultas.
El siguiente paso es identificar los ID de registros que están provocando los registros duplicados:
SELECT *
FROM (
SELECT id, COUNT(*) as cnt
FROM (
SELECT
people.id,
people.first_name,
people.last_name,
phones.phone_number as 'cell'
FROM people
LEFT OUTER JOIN phones
ON phones.person_id=people.id
AND phones.phone_number_type='cell'
) results1
GROUP BY id
) results2
WHERE cnt>1
ORDER BY cnt DESC
En este ejemplo, hemos tenido que utilizar dos subconsultas. La recóndita subconsulta nos da un recuento del numero de teléfono celular para cada persona id. La siguiente consulta es necesaria para tener una cláusula WHERE para el valor CNT. Sólo estamos interesados en los registros duplicados, por lo que sería cuando el recuento es mayor que 1.
Posible Solución
Primero de todo, tenemos que ignorar uno del teléfono celular registros pertenecientes a Ned Flanders. En una situación real, ojalá hubiéramos preferido del pabellón columna o una columna Fecha de modificación para ayudarnos a elegir el registro que se va a conservar y cuáles ignorar. También querrá asegurarse de que el número de teléfono no es NULL o una cadena vacía. En este ejemplo, supongamos que el mayor valor de id es la más reciente y, por lo tanto, la mayoría de registros precisos.
Utilizar un SELECT dentro de la instrucción SELECT.
Una forma de solucionar este problema es utilizar una instrucción SELECT dentro de la instrucción SELECT. De esta manera, se consigue sólo un teléfono celular para cada persona id.
SELECT
people.first_name,
people.last_name,
(
SELECT TOP 1 phone_number
FROM phones
WHERE phones.person_id=people.id
AND phones.phone_number_type='cell'
AND phone_number IS NOT NULL
AND RTRIM(LTRIM(phone_number))!=''
ORDER BY phones.id DESC
) AS 'cell_phone'
FROM people
ORDER BY people.id
En este ejemplo, estamos seleccionando sólo un teléfono celular registro por persona. ¿Qué determina la celda seleccionada teléfono registro es la cláusula ORDER BY; puedes poner tu lógica de negocio aquí para reducirlo a un resultado por persona. Es importante tener chequeos adicionales en la cláusula WHERE, como estamos omitiendo los registros en un intento de seleccionar el mejor registro. No querríamos para seleccionar un valor nulo, sólo porque era la más reciente.
vamos a empezar con el query:
SELECT
id,
person_id,
phone_number,
ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY id DESC) AS row
FROM phones
WHERE phone_number_type='cell'
AND phone_number IS NOT NULL
AND LTRIM(RTRIM(phone_number)) != ''
Él parte especial en esta consulta es utilizar el número de la ROW NUMBER() OVER (...) la sintaxis para obtener un número de fila para cada persona. Es algo raro sintaxis; ayuda a pensar que PARTITION BY como una cláusula GROUP BY. La idea es conseguir una nueva columna denominada fila con un orden con prioridad de 1...n para cada persona id. A continuación, todo lo que tenemos que hacer es tener una cláusula WHERE para especificar que la fila=1.
De nuevo, verá el mismo restringir la cláusula WHERE y la misma cláusula ORDER BY como antes.
query final:
SELECT id, person_id, phone_number
FROM (
SELECT
id,
person_id,
phone_number,
ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY id DESC) AS row
FROM phones
WHERE phone_number_type='cell'
AND phone_number IS NOT NULL
AND LTRIM(RTRIM(phone_number)) != ''
) results
WHERE row=1
La consolidación de varios resultados
Digamos que queríamos mostrar cada uno de los números de teléfono en uno de los campos, separados por comas. ¿Cómo lo hacemos?
SELECT people.*, SUBSTRING(phone_list,1,LEN(phone_list)-1) AS cell_phones
FROM people
CROSS APPLY (
SELECT phone_number+', '
FROM phones
WHERE person_id=people.id
AND phone_number_type = 'cell'
AND phone_number IS NOT NULL
AND LTRIM(RTRIM(phone_number)) != ''
ORDER BY phones.id DESC
FOR XML PATH('')
) results (phone_list)
Esto es bastante extraño consulta. Como puede ver, los resultados son lo que esperábamos, pero ¿cómo hemos llegado aquí? Si nos fijamos en la línea 1, estamos seleccionando todo lo de la tabla personas, junto con una subcadena de la lista de teléfonos. La lista de teléfonos de la columna es una concatenación de un número de teléfono, una coma y un espacio. Si no tomamos una subcadena de la lista de teléfonos de columna, que realmente terminan con una coma y un espacio, lo cual no es deseable.
La Cruz aplicar parte de la consulta es lo que devuelve la coma se unieron a los números de teléfono. Funciona de forma similar a una combinación, pero le permite invocar una función con valores de tabla para cada fila. La sintaxis para el cierre final del paréntesis en la línea 12 es lo que nos permite trabajar con la columna con el nombre de lista de teléfonos en línea 1.
El FOR XML PATH('') es parte de la consulta en línea 11 concatena nuestras filas juntos como una cadena. Esto es realmente un hack de SQL Server es la capacidad de salida XML.
El FOR XML PATH() espera un nombre de etiqueta para que pueda hacer un nodo XML con una etiqueta de inicio y otra de fin; también se une a varias filas juntos como una cadena de texto. Estamos usando una cadena en blanco y por lo tanto no cree una etiqueta, pero no se unen varias filas como una cadena de texto. SQL Server también desea agregar una etiqueta de inicio y otra de fin con el nombre de columna por defecto, pero hemos anexado una coma y un espacio para el número de teléfono seleccione, creando un anónimo selección y por lo tanto evitarse otra etiqueta XML que SQL Server normalmente insertar.
