У меня есть таблица со столбцом 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 - он также мог бы создать вычисляемый целочисленный столбец и поместить в него индекс.
Используйте 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 здесь, это действительно нуль в таблице?