выберите элементы, которые имеют несколько совпадений в нормализованной таблице

ProgramBox

выберите элементы, которые имеют несколько совпадений в нормализованной таблице

Post author:admin
Запись опубликована:17 мая, 2023
Post category:Вопросы по программированию

#php #mysql #search #normalization

#php #mysql #Поиск #нормализация

Вопрос:

У меня есть таблица, в которой хранится множество объектов. Каждый объект может иметь много цветов, которые хранятся в нормализованной таблице, связанной с object_id.

Если я перейду

 SELECT `object_name` FROM `objects`
  LEFT JOIN `object_color` USING `object_id`
    WHERE `object_color` IN ('red', 'blue');

Затем я получаю объекты, которые являются «красными» ИЛИ «синими». Мне нужно получить все объекты, которые являются «красными» И «синими». Если я перейду:

 SELECT `object_name` FROM `objects`
  LEFT JOIN `object_color` USING `object_id`
    WHERE `object_color` = 'red' AND `object_color` = 'blue';

Тогда я ничего не получаю, поскольку в каждой строке есть только один object_color, и это не может быть оба. Также, на самом деле, цвета являются идентификаторами с именами в другой таблице. Я упростил все здесь ради вопроса.

Мне нужно иметь возможность искать неограниченное количество цветов.

Спасибо

Редактировать:

object_color находится только в таблице object_color.

И любой объект будет иметь любой единственный цвет один раз.

Ответ №1:

 Select object_name
From objects
Where object_color In('red','blue')
Group By object_name
Having Count(Distinct object_color) = 2

Кстати, вы никогда не упоминаете, из какой таблицы происходит object_color столбец. Если это из object_color таблицы:

 Select O.object_name
From objects As O
    Join object_color As C
        On C.object_id = O.object_id
Where C.object_color In('red','blue')
Group By O.object_name
Having Count(Distinct C.object_color) = 2

Приведенный выше запрос предполагает, что в данной object строке могло не быть нескольких object_color строк одного цвета. Однако, как отметил Джоэл Си, если бы было возможно object иметь несколько object_color строк красного или синего цвета, то для этого требовался другой запрос. :

 Select ...
From objects As O
Where O.object_id In    (
                        Select C1.object_id
                        From object_color As C1
                        Where C1.object_color = 'red'
                        )
    And O.object_id In  (
                        Select C1.object_id
                        From object_color As C1
                        Where C1.object_color = 'blue'
                        )

Еще одно решение:

 Select O.object_name
From objects As O
    Join    (
            Select C1.object_id, C1.object_color
            From object_color As C1
            Where C1.color In('red','blue')
            Group By C1.object_id, C1.object_color
            ) As Z
        On Z.object_id = O.object_id
Group By O.object_name
Having Count(*) = 2

1. Что, если для объекта существует несколько записей ‘red’ и / или ‘blue’? Это даст вам результаты, в которых ‘red’ появляется дважды и никогда не синий, но исключит результаты, в которых ‘red’ и ‘blue’ появляются несколько раз.

2. @Joel C — Это важная часть информации о схеме, которая не указана в OP. Если бы это было возможно, то я бы, вероятно, обработал это с помощью нескольких предложений In .

3. @Thonas: и @Joel: Разве вы Distinct C.object_color уже не позаботились об этой проблеме?

4. @ypercube — Сегодня недостаточно кофе. Вы правы. Я уже обработал это через distinct.

5. Каждый объект может иметь неограниченное количество различных цветов. Ни один цвет не будет отображаться на объекте дважды. Я думал о нескольких операторах ‘IN’ для каждого цвета, но мне показалось, что это могло бы привести к множеству подзапросов, если бы было много цветов. Не рассматривал «наличие количества», которое кажется довольно скользким.

Ответ №2:

Я предпочитаю, ON а не USING :

 SELECT o.object_name
FROM objects o
  JOIN object_color oc
    ON o.object_id = oc.object_id
WHERE oc.object_color IN ( 'red', 'blue' )
GROUP BY o.object_id
HAVING COUNT(o.object_id) = ( SELECT COUNT(*)
                              FROM ( 'red', 'blue' )
                            )

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

Ответ №3:

Вам понадобится мультисоединение

 SELECT `object_name` FROM `objects`
  LEFT JOIN `object_color` USING `object_id`
    WHERE `object_color` = 'red'
  LEFT JOIN `object_color` USING `object_id`
    WHERE `object_color` = 'blue' ;

Ответ №4:

Вы также можете использовать INTERSECT, если он поддерживается в используемом вами SQL.

 SELECT object_name
FROM objects o, objects_color oc
WHERE object_color = 'red'
and o.object_id = oc.object_id

INTERSECT

SELECT object_name
FROM objects o, objects_color oc
WHERE object_color = 'blue'
and o.object_id = oc.object_id

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