Cómo consultar y obtener un conjunto de resultados que contenga como mínimo el CONJUNTO de valores enviados
WITH fData AS
(
SELECT 1001 AS lookupID, 'A' AS LookUpValue UNION ALL
SELECT 1001 AS lookupID, 'B' AS LookUpValue UNION ALL
SELECT 1001 AS lookupID, 'C' AS LookUpValue UNION ALL
SELECT 1001 AS lookupID, 'D' AS LookUpValue UNION ALL
SELECT 1002 AS lookupID, 'A' AS LookUpValue UNION ALL
SELECT 1002 AS lookupID, 'Z' AS LookUpValue UNION ALL
SELECT 1002 AS lookupID, 'S' AS LookUpValue UNION ALL
SELECT 1002 AS lookupID, 'J' AS LookUpValue UNION ALL
SELECT 1003 AS lookupID, 'H' AS LookUpValue UNION ALL
SELECT 1003 AS lookupID, 'I' AS LookUpValue UNION ALL
SELECT 1003 AS lookupID, 'Z' AS LookUpValue UNION ALL
SELECT 1003 AS lookupID, 'C' AS LookUpValue
)
SELECT *
FROM fData fd
WHERE fd.LookUpValue IN ('A','B','C','D') /* This pulls back ANY record having these values and then return all of the records in that list. I want ONLY lists that have a minimum of these values. For example of my list only contained 'A' I would see both List 1001 records and List 1002 Records. */
;
El código que proporcioné simplemente contiene tres listas falsas. Cada lista contiene un conjunto de valores. ¿Cómo escribo una consulta que me proporcione todos los registros de la lista que contienen los valores que especifico? El uso de una instrucción IN no funciona ya que me da TODAS las listas (registros) que contienen esos valores de lista. En su lugar, necesito devolver solo los registros de lista que contienen al menos todos los valores que especifiqué.
En mi ejemplo, dado que proporcioné cuatro valores ('A', 'B', 'C', 'D'), solo debería ver en mi conjunto de resultados los registros de la primera lista con lookupID 1001. Si, por ejemplo, envío un único valor de 'A' Entonces vería todos los registros para la Lista 1001 y la Lista 1002 ya que ambas listas contienen como mínimo el valor 'A'. La lista 1003 no tiene ningún valor de 'A'.
Respuestas
Obtienes los lookupID
s que quieres con esta consulta:
SELECT lookupID
FROM fData
WHERE LookUpValue IN ('A','B','C','D')
GROUP BY lookupID
HAVING COUNT(DISTINCT LookUpValue) = 4 -- the number of searched lookupvalues
y todas las filas de la tabla con el uso del operador IN
:
SELECT * FROM fData
WHERE lookupID IN (
SELECT lookupID
FROM fData
WHERE LookUpValue IN ('A','B','C','D')
GROUP BY lookupID
HAVING COUNT(DISTINCT LookUpValue) = 4
)
El código es SQL estándar y funciona en cualquier base de datos (que yo sepa).
Vea la demostración .
Resultados:
> lookupID | LookUpValue
> -------: | :----------
> 1001 | A
> 1001 | B
> 1001 | C
> 1001 | D
Una variación de la consulta de forpas usando agregados de ventana en lugar de Agrupar por
WITH fData AS
(
SELECT 1001 AS lookupID, 'A' AS LookUpValue UNION ALL
SELECT 1001 AS lookupID, 'B' AS LookUpValue UNION ALL
SELECT 1001 AS lookupID, 'C' AS LookUpValue UNION ALL
SELECT 1001 AS lookupID, 'D' AS LookUpValue UNION ALL
SELECT 1001 AS lookupID, 'E' AS LookUpValue UNION ALL
SELECT 1002 AS lookupID, 'A' AS LookUpValue UNION ALL
SELECT 1002 AS lookupID, 'Z' AS LookUpValue UNION ALL
SELECT 1002 AS lookupID, 'S' AS LookUpValue UNION ALL
SELECT 1002 AS lookupID, 'J' AS LookUpValue UNION ALL
SELECT 1003 AS lookupID, 'H' AS LookUpValue UNION ALL
SELECT 1003 AS lookupID, 'I' AS LookUpValue UNION ALL
SELECT 1003 AS lookupID, 'Z' AS LookUpValue UNION ALL
SELECT 1003 AS lookupID, 'C' AS LookUpValue
),
cte as
(
SELECT *,
count(case when LookUpValue IN ('A','B','C','D') then 1 end)
over (partition by lookupID) as cnt
FROM fData
)
select lookupID, LookUpValue
from cte
where cnt = 4
Esto solo funciona si la combinación lookupID | LookUpValue es única (esto también simplificaría la consulta forpas al eliminar DISTINCT).
Ver violín