Tabla de ejemplo:

ID, foreign_id, status

El ID es único , el foreign_id no lo es. Filas de ejemplo:

1, 1111, 0  
2, 2222, 0  
3, 3333, 1
4, 1111, 1
5, 4444, 0

Lo que quiero seleccionar son los únicos foreign_id que solo tienen el estado 0. Si tiene el estado 0 pero otra fila con la misma ID extranjera tiene el estado 1, no seleccione {{ X1}}. Rendimiento esperado:

1, 2222, 0
5, 4444, 0

Puedo seleccionar dónde status = 0 y agrupar por foreign_id, pero eso solo seleccionará la fila donde el estado es 0, aunque puede haber otra fila con el mismo foreign_id y el estado como 1.

SELECT ID, foreign_id 
FROM table 
WHERE status = 0 
GROUP BY foreign_id

Cualquier ayuda apreciada.

2
skyz 13 sep. 2018 a las 14:51

5 respuestas

La mejor respuesta

Utilizando GROUP_CONCAT y la cláusula DISTINCT, nosotros grupo todos los estados únicos contra un foreign_id. Ahora dentro de la cláusula HAVING, solo necesitamos filtrar solo aquellos foreign_id, donde los estados únicos son solo '0'.

Pruebe lo siguiente:

SELECT ID, foreign_id, status, GROUP_CONCAT(DISTINCT status) as statuses 
FROM table 
GROUP BY foreign_id 
HAVING statuses = '0'
1
Madhur Bhaiya 13 sep. 2018 a las 11:53

Como han dicho otros, debe agregar los datos, pero hay muchos problemas potenciales con esto. Pero antes de llegar a eso, usted ha declarado que desea los registros originales en la salida, no los datos consolidados, la solución más simple es usar 2 consultas, una para identificar los Foreign_ids con solo estado = 0 y otra para obtener el correspondiente filas

SELECT r.*
FROM examples r
JOIN ( SELECT s.foreign_id, MAX(s.status)
  FROM examples s
  GROUP BY s.foreign_id
  HAVING MAX(s.status)=0
) t
ON r.foreign_id=t.foreign_id

Alternativamente, podría obtener los Foreign_ids donde status! = 0 y excluir aquellos en la segunda consulta, lo que podría ser más eficiente dependiendo de la distribución de sus datos.

Cuando esto se despega es en el caso de "estado" <0. La salida incluirá estas filas (potencialmente lo mismo puede suceder con SUM ()). Hay varias formas de abordar esto, por ejemplo, podría SUMAR (ABS (estado)) o SUMA (SI (estado = 0,0,1)). Como Madhur sugiere, también podría GROUP_CONCAT (estado) y verificar que la salida de la columna sea solo '0'.

Pero sospecho que está a punto de decirme que el estado no puede ser negativo (¿o nulo?) Ya que es un número nominal. Si ese es el caso, entonces está usando el tipo de datos incorrecto, debería estar usando un ENUM. Si ese es el caso, entonces no puede aplicar SUM () o MAX () a un ENUM pero aún puede usar los métodos SUM (IF (status = ?, 0,1)) o GROUP_CONCAT ().

Por lo tanto....

SELECT r.*
FROM examples r
JOIN ( SELECT s.foreign_id, SUM(IF(status=0,0,1))
  FROM examples s
  GROUP BY s.foreign_id
  HAVING SUM(IF(status=0,0,1))=0
) t
ON r.foreign_id=t.foreign_id
1
symcbean 13 sep. 2018 a las 12:22

(Edité esto para demostrar que funciona y para aclarar algunas cosas):

Lo que probablemente le interese es la declaración HAVING.

Puede usar una declaración HAVING después de GROUP BY. Aquí hay una muy buena explicación: https://www.dofactory.com/sql/having

En resumen, lo que hace la instrucción HAVING es permitirle filtrar sus datos en los retornos como resultado de su expresión GROUP BY.

Prueba esto:

SELECT 
     foreign_id, MAX(status) AS status
FROM
     myTable
GROUP BY 
     foreign_id
HAVING 
     MAX(status) = 0



Aquí hay un ejemplo de su funcionamiento (probado en un archivo básico de MS Access):
ingrese la descripción de la imagen aquí

Luego ingresé este código:
ingrese la descripción de la imagen aquí

Y obtuve estos resultados:
ingrese la descripción de la imagen aquí



¡Hazme saber si tienes alguna pregunta!

1
wundermahn 13 sep. 2018 a las 13:25

Usar la función agregada

select * from t where foreign_id in (
     select foreign_id
        from t
        group by foreign_id
        having min(status) = 0
       ) and status!=1
0
Zaynul Abadin Tuhin 13 sep. 2018 a las 11:56

Puede usar group by y having:

select foreign_id, max(status) as status
from t
group by foreign_id
having max(status) = 0;

Básicamente, debe hacer la prueba después de la group by.

Si desea las filas completas, use not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.foreign_id = t.foreign_id and t2.status <> 0
                 );
1
Gordon Linoff 13 sep. 2018 a las 11:54