SELF JOIN для получения отдельных строк
У меня есть таблица со столбцом type ENUM('A', 'B'), parent, score, userid
Все строки с типом A имеют родителя (внешний ключ), а B имеют родителя типа A(id)
Теперь я хочу выбрать все те строки A, чей дочерний элемент (т.е. тип B) является not идентификатором пользователя u1.
Запрос, который я пробовал:
select distinct A.id from tableName as A
LEFT JOIN tableName as B
ON A.id = B.parent
WHERE A.type = 'A'
AND (B.parent is null or B.user_id != 'u1')
С помощью этого запроса можно получить все записи, у которых нет дочерних элементов (тип B); Однако появляются такие случаи, как id=4, чей user_id является u2, что не ожидается, поскольку parent=2 также разделяется id=5.
Текущий результат:
Ожидал:
Хотя это можно сделать с помощью внутреннего запроса, размер моей таблицы очень велик, и внутренний не выглядит хорошим решением. Есть ли способ сделать это только с соединениями.
Да, это строка. Изменил запрос, это было то, что я пробовал с правильным соединением.
@forpas Извинения. В спешке я сделал несколько ошибок в вопросе. Исправили. Это последняя версия, которую я пробовал. Можно проверить на той же скрипке. dbfiddle.uk/DP08rh4-
Вы сможете значительно повысить производительность, изменив соединение fk, чтобы использовать NULL, или 0, или -1, или какое-то подобное значение, чтобы вы могли преобразовать это в целочисленный столбец.
Если бы была такая возможность, я бы вообще сделал отдельную таблицу. Столбец не может быть изменен, по крайней мере, в течение следующих нескольких месяцев.
@JoelCoehoorn - он также мог бы создать вычисляемый целочисленный столбец и поместить в него индекс.
Ответы 1
Используйте NOT EXISTS:
SELECT t1.*
FROM tablename t1
WHERE t1.type = 'A'
AND NOT EXISTS (
SELECT *
FROM tablename t2
WHERE t2.parent = t1.id AND t2.user_id = 'u1'
);
Смотрите демо.
Как будет производительность для внутреннего запроса, учитывая огромный размер таблицы?
@MarkhorKiller EXISTS обычно работает лучше, чем объединение, потому что ему не нужно сканировать всю таблицу, а возвращается, как только находит совпадение. Кроме того, индексы не помешали бы. Вам нужен индекс по типу и составной индекс по родителю, user_id. В любом случае вы должны попробовать код с вашими реальными данными.
Да, уже есть индексы, размещенные в соответствующих столбцах. попробуй. Итак, далеко выглядит хорошо.
Итак, родительский столбец — это строка? или когда у вас есть fk здесь, это действительно нуль в таблице?