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

Ответ 1

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

Кому-нибудь, кто это читает: мой ответ прост и понятен и получил "принятый" статус, но, пожалуйста, прочитайте ответ, указанный @cletus. Он имеет гораздо лучшую производительность.

Подумав вслух, другой способ написать самосоединение, описанный @cletus:

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

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

Я запросил этот запрос за 2,1 миллиона строк (дамп данных Qaru July для PostTags), используя MySQL 5.1.48 на моем Macbook, и он вернул результат за 1.08 сек. На достойном сервере с достаточным объемом памяти, выделенным для innodb_buffer_pool_size, он должен быть еще быстрее.

Ответ 2

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

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 с

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 с

Это правильно. Предлагаемая версия соединения в двадцать раз быстрее, чем сводная версия.

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

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

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

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

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

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

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

Ответ 3

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

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

Ответ 4

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

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

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

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

Я также предполагаю, что все соответствующие столбцы не являются NULLable, поэтому нет никаких сюрпризов в 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
  )
);

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

Ответ 5

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

Разве это не решит проблему? Насколько хорошо это решение на типичных реляционных БД? Будет ли оптимизатор запросов автоматически оптимизировать это?

Ответ 6

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