Cómo consultar y obtener un conjunto de resultados que contenga como mínimo el CONJUNTO de valores enviados

Aug 21 2020
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

3 forpas Aug 21 2020 at 18:02

Obtienes los lookupIDs 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         
dnoeth Aug 21 2020 at 21:51

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