Выберите значения, которые соответствуют различным условиям в разных строках?

Принимая идентификатор пользователя, roleid содержатся в уникальном индексе (значение, что не может быть 2 записей где идентификатор пользователя = x и roleid = 1

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

Если Вам нужен какой-либо вид общности здесь (различные комбинации с 3 ролями или различные n-ролевые комбинации)... Я предложил бы, чтобы Вы использовали немного систему маскирования для своих ролей и использовали побитовые операторы для выполнения запросов...

Хорошо, я получил downvoted на этом так, я решил протестировать его:

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

CREATE INDEX ON userrole (roleid);

Выполнение это:

<?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";
    }
}
?>

Вывод:

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

, Который добавляет 500 000 случайных комбинаций пользовательской роли и существуют приблизительно 25 000, которые соответствуют выбранным критериям.

Первый запрос:

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

Время запроса: 0.312 времени запроса s

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

: 0,016 s

Правильно. Версия соединения, которую я предложил, в двадцать раз быстрее, чем совокупная версия.

Жаль, но я делаю это для жизни и работы в реальном мире и в реальном мире, мы тестируем SQL, и результаты выступают за себя.

причина этого должна быть довольно ясной. Агрегатный запрос масштабируется в стоимости с размером таблицы. Каждая строка обработана, агрегирована и фильтрована (или не) через HAVING пункт. Версия соединения будет (использование индекса) выбирают подмножество пользователей на основе данной роли, затем проверяют что подмножество по второй роли и наконец что подмножество против третьей роли. Каждый выбор (в алгебра отношений условия) работает над все больше небольшим подмножеством. От этого можно завершить:

производительность версии соединения становится еще лучше с более низким падением соответствий.

, Если было только 500 пользователей (из 500k образца выше), который имел три установленных роли, версия соединения станет значительно быстрее. Совокупная версия не будет (и любое повышение производительности является результатом переноса 500 пользователей вместо 25k, который версия соединения, очевидно, получает также).

мне было также любопытно видеть, как реальная база данных (т.е. Oracle) будет иметь дело с этим. Таким образом, я в основном повторил то же осуществление на Oracle, XE (работающий на той же настольной машине Windows XP как MySQL от предыдущего примера) и результаты почти идентичен.

Соединения, кажется, осуждены, но как я продемонстрировал, агрегатные запросы могут быть порядком величины медленнее.

Обновление: приблизительно после обширное тестирование , изображение более сложно, и ответ будет зависеть от Ваших данных, Вашей базы данных и других факторов. Мораль истории является тестом, тестом, тестом.

Классический способ сделать это - рассматривать это как проблему реляционного деления.

На английском языке: выберите тех пользователей, для которых не пропущено ни одно из желаемых значений roleid.

Я предполагаю, что у вас есть таблица Users, на которую ссылается таблица UserRole, и я предполагаю, что желаемые значения roleid находятся в таблица:

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);

Я также предполагаю, что все соответствующие столбцы не имеют значения NULL, поэтому нет никаких сюрпризов с IN или NOT EXISTS. Вот SQL-запрос, который выражает английский выше:

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
  )
);

Другой способ написать это

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
  )
);

Это может оказаться эффективным, а может и не оказаться эффективным, в зависимости от индексов, платформы, данных и т. Д. Поищите в Интернете "реляционное разделение" "и вы найдете много.