valores - where con varias condiciones mysql

Seleccione valores que cumplan condiciones diferentes en filas diferentes?

Question

Esta es una consulta muy básica que no puedo entender ...

Digamos que tengo una tabla de dos columnas como esta:

userid | roleid
--------|--------
1 | 1
1 | 2
1 | 3
2 | 1

Quiero obtener todos los ID de usuario distintos que tienen los roleids 1, 2 Y 3. Utilizando el ejemplo anterior, el único resultado que quiero devolver es userid 1. ¿Cómo hago esto?

Answer 1

Asumiendo userid, roleid están contenidos en un índice único (lo que significa que no puede haber 2 registros donde userid = x y roleid = 1

select count(*), userid from t
where roleid in (1,2,3)
group by userid
having count(*) = 3

Answer 2

La forma clásica de hacerlo es tratarlo como un problema de división relacional.

En inglés: seleccione aquellos usuarios para los que no falta ninguno de los valores de roleid deseados.

Supongo que tiene una tabla de Usuarios a la que hace referencia la tabla UserRole, y supongo que los valores de roleid deseados se encuentran en una tabla:

create table RoleGroup(
roleid int not null,
primary key(roleid)
)
insert into RoleGroup values (1);
insert into RoleGroup values (2);
insert into RoleGroup values (3);

También asumiré que todas las columnas relevantes no son NULL, por lo que no hay sorpresas con IN o NOT EXISTS. Aquí hay una consulta SQL que expresa el inglés anterior:

select userid from Users as U
where not exists (
select * from RoleGroup as G
where not exists (
select R.roleid from UserRole as R
where R.roleid = G.roleid
and R.userid = U.userid
)
);

Otra forma de escribirlo es esto

select userid from Users as U
where not exists (
select * from RoleGroup as G
where G.roleid not in (
select R.roleid from UserRole as R
where R.userid = U.userid
)
);

Esto puede o no ser eficiente, dependiendo de índices, plataforma, datos, etc. Busque en la web "división relacional" y encontrará mucho.

Answer 3

Ok, recibí downvoted por esto, así que decidí probarlo:

CREATE TABLE userrole (
userid INT,
roleid INT,
PRIMARY KEY (userid, roleid)
);
CREATE INDEX ON userrole (roleid);

Ejecuta esto:

<?php
ini_set(''max_execution_time'', 120); // takes over a minute to insert 500k+ records
$start = microtime(true);
echo "<pre>/n";
mysql_connect(''localhost'', ''scratch'', ''scratch'');
if (mysql_error()) {
echo "Connect error: " . mysql_error() . "/n";
}
mysql_select_db(''scratch'');
if (mysql_error()) {
echo "Selct DB error: " . mysql_error() . "/n";
}
$users = 200000;
$count = 0;
for ($i=1; $i<=$users; $i++) {
$roles = rand(1, 4);
$available = range(1, 5);
for ($j=0; $j<$roles; $j++) {
$extract = array_splice($available, rand(0, sizeof($available)-1), 1);
$id = $extract[0];
query("INSERT INTO userrole (userid, roleid) VALUES ($i, $id)");
$count++;
}
}
$stop = microtime(true);
$duration = $stop - $start;
$insert = $duration / $count;
echo "$count users added./n";
echo "Program ran for $duration seconds./n";
echo "Insert time $insert seconds./n";
echo "</pre>/n";
function query($str) {
mysql_query($str);
if (mysql_error()) {
echo "$str: " . mysql_error() . "/n";
}
}
?>

Salida:

499872 users added.
Program ran for 56.5513510704 seconds.
Insert time 0.000113131663847 seconds.

Eso agrega 500,000 combinaciones de roles de usuario aleatorios y hay aproximadamente 25,000 que coinciden con los criterios elegidos.

Primera consulta:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

Tiempo de consulta: 0.312s

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

Tiempo de consulta: 0.016s

Está bien. La versión de unión que propuse es veinte veces más rápida que la versión global.

Lo siento pero hago esto para vivir y trabajar en el mundo real y en el mundo real probamos SQL y los resultados hablan por sí mismos.

La razón para esto debería ser bastante clara. La consulta agregada se escalará en costo con el tamaño de la tabla. Cada fila se procesa, agrega y filtra (o no) a través de la cláusula HAVING . La versión de unión (utilizando un índice) seleccionará un subconjunto de usuarios en función de un rol dado, luego verificará ese subconjunto con el segundo rol y, finalmente, ese subconjunto con el tercer rol. Cada selection (en términos de álgebra relacional) funciona en un subconjunto cada vez más pequeño. De esto puedes concluir:

El rendimiento de la versión de unión mejora aún más con una menor incidencia de coincidencias.

Si solo había 500 usuarios (de la muestra de 500k anterior) que tenían los tres roles indicados, la versión de unión se volverá significativamente más rápida. La versión agregada no lo hará (y cualquier mejora en el rendimiento es el resultado de transportar 500 usuarios en lugar de 25k, lo que obviamente también obtiene la versión de unión).

También tenía curiosidad por ver cómo una verdadera base de datos (es decir, Oracle) se ocuparía de esto. Así que básicamente repetí el mismo ejercicio en Oracle XE (ejecutándose en la misma máquina de escritorio de Windows XP que el MySQL del ejemplo anterior) y los resultados son casi idénticos.

Las uniones parecen estar mal vistas, pero como he demostrado, las consultas agregadas pueden ser de un orden de magnitud más lentas.

Actualización: después de algunas pruebas exhaustivas , la imagen es más complicada y la respuesta dependerá de sus datos, su base de datos y otros factores. La moraleja de la historia es prueba, prueba, prueba.

Answer 4

Si necesita algún tipo de generalidad aquí (diferentes combinaciones de 3 roles o diferentes combinaciones de n-roles) ... Le sugiero que use un sistema de enmascaramiento de bits para sus roles y use los operadores bit a bit para realizar sus consultas ...

Answer 5

SELECT userid
FROM UserRole
WHERE roleid IN (1, 2, 3)
GROUP BY userid
HAVING COUNT(DISTINCT roleid) = 3;

Para cualquiera que lea esto: mi respuesta es simple y directa, y obtuve el estado ''aceptado'', pero por favor lea la answer dada por @cletus. Tiene un rendimiento mucho mejor.

Apenas pensando en voz alta, otra forma de escribir la auto-unión descrita por @cletus es:

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid
JOIN userrole t3 ON t2.userid = t3.userid
WHERE (t1.roleid, t2.roleid, t3.roleid) = (1, 2, 3);

Esto podría ser más fácil de leer para usted, y MySQL admite comparaciones de tuplas como esa. MySQL también sabe cómo utilizar los índices de cobertura de forma inteligente para esta consulta. Simplemente ejecute EXPLAIN y vea "Usar índice" en las notas para las tres tablas, lo que significa que está leyendo el índice y que ni siquiera tiene que tocar las filas de datos.

Ejecuté esta consulta en más de 2,1 millones de filas (el volcado de datos de July para PostTags) usando MySQL 5.1.48 en mi Macbook, y devolvió el resultado en 1.08 segundos. En un servidor decente con suficiente memoria asignada a innodb_buffer_pool_size , debería ser aún más rápido.

Answer 6

select userid from userrole where userid = 1
intersect
select userid from userrole where userid = 2
intersect
select userid from userrole where userid = 3

¿Esto no resolverá el problema? ¿Qué tan buena es esta solución en las DB relacionales típicas? ¿El optimizador de consultas optimizará esto automáticamente?