SELF JOIN для получения отдельных строк

У меня есть таблица со столбцом type ENUM('A', 'B'), parent, score, userid

Все строки с типом A имеют родителя (внешний ключ), а B имеют родителя типа A(id)

идентификатор тип родитель счет ID пользователя 1 А фк 1 u1 2 А фк 5 u1 3 Б 1 2 u1 4 Б 2 4 U2 5 Б 2 2 u1 6 А фк 4 уз

Теперь я хочу выбрать все те строки 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.

Текущий результат:

идентификатор тип родитель счет ID пользователя 2 А фк 5 u1 6 А фк 4 уз

Ожидал:

идентификатор тип родитель счет ID пользователя 6 А фк 4 уз

Хотя это можно сделать с помощью внутреннего запроса, размер моей таблицы очень велик, и внутренний не выглядит хорошим решением. Есть ли способ сделать это только с соединениями.

Итак, родительский столбец — это строка? или когда у вас есть fk здесь, это действительно нуль в таблице?

Hogan 10.01.2023 16:58

Да, это строка. Изменил запрос, это было то, что я пробовал с правильным соединением.

MarkhorKiller 10.01.2023 17:05

@forpas Извинения. В спешке я сделал несколько ошибок в вопросе. Исправили. Это последняя версия, которую я пробовал. Можно проверить на той же скрипке. dbfiddle.uk/DP08rh4-

MarkhorKiller 10.01.2023 17:48

Вы сможете значительно повысить производительность, изменив соединение fk, чтобы использовать NULL, или 0, или -1, или какое-то подобное значение, чтобы вы могли преобразовать это в целочисленный столбец.

Joel Coehoorn 10.01.2023 17:57

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

MarkhorKiller 10.01.2023 17:59

@JoelCoehoorn - он также мог бы создать вычисляемый целочисленный столбец и поместить в него индекс.

Hogan 10.01.2023 18:14
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
1
6
56
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 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 10.01.2023 17:55

@MarkhorKiller EXISTS обычно работает лучше, чем объединение, потому что ему не нужно сканировать всю таблицу, а возвращается, как только находит совпадение. Кроме того, индексы не помешали бы. Вам нужен индекс по типу и составной индекс по родителю, user_id. В любом случае вы должны попробовать код с вашими реальными данными.

forpas 10.01.2023 18:00

да, уже есть индексы, размещенные в соответствующих столбцах. попробуй. Итак, далеко выглядит хорошо.

MarkhorKiller 10.01.2023 18:07

Другие вопросы по теме