Отфильтруйте запрос «один ко многим», потребовав соответствия всем критериям.

Представьте себе следующие таблицы:

создать табличные поля (id int, name text, ...);

создать таблицу вещей в ящиках (id int, box_id int, вещь перечисление («яблоко», «банан», «апельсин»);

И таблицы выглядят так:

Boxes:
id | name
1  | orangesOnly
2  | orangesOnly2
3  | orangesBananas
4  | misc

thingsinboxes:
id | box_id | thing
1  |  1     | orange
2  |  1     | orange
3  |  2     | orange
4  |  3     | orange
5  |  3     | banana
6  |  4     | orange
7  |  4     | apple
8  |  4     | banana

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

Как это масштабируется, если предположить, что у меня есть несколько сотен тысяч коробок и, возможно, миллион вещей в коробках?

Я хотел бы сохранить все это в SQL, если это возможно, а не обрабатывать набор результатов с помощью скрипта.

Я использую как postgres, так и mysql, поэтому подзапросы, вероятно, плохи, учитывая, что mysql не оптимизирует подзапросы (во всяком случае, до версии 6).


mysql sql join one-to-many
person Sam    schedule 26.01.2009    source источник
comment
Я не совсем понимаю, чего вы пытаетесь достичь, но структура вашей таблицы довольно странная!   -  person Joe Phillips    schedule 27.01.2009
comment
С чего вы взяли, что MySQL не оптимизирует подзапросы?   -  person Bill Karwin    schedule 27.01.2009
comment
Структура таблицы мне кажется нормальной.   -  person thethinman    schedule 12.12.2009

Ответы (2)


arrow_upward
5
arrow_downward 

SELECT b.*
FROM boxes b JOIN thingsinboxes t ON (b.id = t.box_id)
GROUP BY b.id
HAVING COUNT(DISTINCT t.thing) = 1 AND SUM(t.thing = 'orange') > 0;

Вот еще одно решение, которое не использует GROUP BY:

SELECT DISTINCT b.*
FROM boxes b
  JOIN thingsinboxes t1 
    ON (b.id = t1.box_id AND t1.thing = 'orange')
  LEFT OUTER JOIN thingsinboxes t2 
    ON (b.id = t2.box_id AND t2.thing != 'orange')
WHERE t2.box_id IS NULL;

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

person Bill Karwin    schedule 26.01.2009
comment
Поскольку HAVING запускается после всего остального, этот запрос создает гигантскую временную таблицу, а затем запускает в ней фильтры. В приведенном выше сценарии масштабируемости это чрезвычайно дорогой подход. Наверняка есть что-то более эффективное? - person Sam; 27.01.2009
comment
Сэм: Сомневаюсь, что оптимизатор запросов создаст большую временную таблицу — поскольку он знает, что ему нужно ГРУППИРОВАТЬ ПО b.id, он может генерировать по одной строке за раз в порядке b.id и отслеживать количество различных вещей. и количество апельсинов в последнем отрезке строк с одинаковыми ставками. - person j_random_hacker; 27.01.2009
comment
@Sam: Вы ограничили решение, сказав, что не хотите использовать подзапросы. - person Bill Karwin; 27.01.2009
comment
Второй запрос работает намного лучше (на порядки), чем первый, по крайней мере, в моей ситуации, вероятно, из-за огромного размера таблиц. Это также достаточно быстро, когда поиск является регулярным выражением вместо =. Это может быть эквивалентно подзапросу, но mysql им не захлебнется. - person Sam; 27.01.2009

arrow_upward
2
arrow_downward

Я думаю, что запрос Билла Карвина просто прекрасен, однако, если относительно небольшая часть коробок содержит апельсины, вы сможете ускорить процесс, используя индекс в поле thing:

SELECT b.*
FROM boxes b JOIN thingsinboxes t1 ON (b.id = t1.box_id)
WHERE t1.thing = 'orange'
AND NOT EXISTS (
    SELECT 1
    FROM thingsinboxes t2
    WHERE t2.box_id = b.id
    AND t2.thing <> 'orange'
)
GROUP BY t1.box_id

Подзапрос WHERE NOT EXISTS будет выполняться только один раз для каждой оранжевой вещи, так что это не слишком дорого, если апельсинов не много.

person j_random_hacker    schedule 26.01.2009
comment
Этот попал мне в точку. - person thethinman; 12.12.2009