Сможете ли вы решить этот простой SQL-запрос?

Предположим, это веб-сайт, на котором продаются фотоаппараты. Вот мои сущности (таблицы):

Camera: A simple camera
Feature: A feature like: 6mp, max resolution 1024x768,

Дело в том, что между камерами и функцией у меня связь "многие ко многим", поэтому у меня есть дополнительная таблица:

camera -> cameras_features -> feature

Итак, запрос прост:

Как получить все камеры с функциями 1,2 и 3?

Это похоже на построение индекса растрового изображения.

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

C1 has features 1,2,3
C2 has features 1,2,4
C3 has features 1,2

Вот вопросы и ожидаемый результат:

  • Показать все камеры с функциями 1, 2 и 3: C1
  • Показать все камеры с функциями 1, 2 и 4: C2
  • Показать все камеры с функциями 1 и 2: C1, C2 и C3

Вот что я сделал (работает, но действительно некрасиво, не хочу его использовать):

SELECT * FROM camera c

WHERE c.id IN (    
    (SELECT c.id FROM camera c JOIN cameras_features f ON (c.id=f.camera_id)
    WHERE f.feature_id=1)
        q1 JOIN -- simple intersect
    (SELECT c.id FROM camera c JOIN cameras_features f ON (c.id=f.camera_id)
    WHERE f.feature_id=2)
        q2 JOIN ON (q1.id=q2.id)
)

mysql sql relational-division sql-match-all
person santiagobasulto    schedule 01.02.2012    source источник
comment
Мне стыдно, это не домашнее задание, но с меткой все в порядке.   -  person santiagobasulto    schedule 02.02.2012

Ответы (3)


arrow_upward
3
arrow_downward 

SELECT DISTINCT Camera.*
FROM Camera c
     INNER JOIN cameras_features fc1 ON c.id = fc1.camera_id AND fc1.feature_id = 1
     INNER JOIN cameras_features fc2 ON c.id = fc2.camera_id AND fc2.feature_id = 2

Здесь происходит то, что камеры будут отфильтрованы до камер с функцией 1, а затем в этой группе камеры будут отфильтрованы до камер с функцией 2.

person Bassam Mehanni    schedule 01.02.2012
comment
Это работает! Я не могу передать эмоции, которые испытываю сейчас. Это похоже на то, что я сделал, но как это называется с трюком JOINS? - person santiagobasulto; 02.02.2012
comment
И вам, вероятно, не нужен DISTINCT. - person ypercubeᵀᴹ; 02.02.2012
comment
См. Также этот вопрос: Как фильтровать результаты SQL в отношении has-many-through с десятками других способов достижения тех же результатов, а также с тестами (они предназначены для Postgres, но вы увидите, что метод multiple JOIN довольно быстро). - person ypercubeᵀᴹ; 02.02.2012
comment
не уверен, имеет ли внутренняя фильтрация соединений конкретное имя, но рад, что могу помочь. @ypercube не уверен, нужно мне это или нет в MySQL, но в MS SQL будет как минимум 2 записи на камеру. - person Bassam Mehanni; 02.02.2012
comment
Если (camera_id, feature_id) равно UNIQUE в таблице cameras_features (и он должен быть уникальным), то вам не нужен distinct. - person ypercubeᵀᴹ; 02.02.2012
comment
Хороший вопрос, ничего подобного раньше не нашел. Похоже, что ответ HAVING лучше EXPLAIN, но я должен добавить данные и сравнить их - person santiagobasulto; 02.02.2012
comment
@ypercube, разве мы не получаем запись для функции 1 и запись для функции 2? - person Bassam Mehanni; 02.02.2012

arrow_upward
5
arrow_downward 

SELECT camera.id
FROM camera JOIN camera_features ON camera.id=camera_features.camera_id
GROUP BY camera.id
HAVING sum(camera_features.feature_id IN (1,2,3))=3

3 - количество функций в (1,2,3). И если предположить, что (camera_id,feature_id) уникален в camera_features.

person Michael Krelin - hacker    schedule 01.02.2012
comment
Это предположение в порядке. На самом деле у меня УНИКАЛЬНО на этих столбцах. - person santiagobasulto; 02.02.2012
comment
Нет, я уже думал наперед, спасибо, исправлено. (это было о группе). - person Michael Krelin - hacker; 02.02.2012
comment
И я предполагаю, что вы можете получить остальное из своих таблиц, имеющих список идентификаторов, либо втиснув его в этот запрос, либо используя его как подзапрос. Ваша попытка предполагает, что вы на это способны :) - person Michael Krelin - hacker; 02.02.2012
comment
Оно работает! Действительно хороший материал! Что касается изменения моих таблиц, я упростил пример;) Это немного сложнее. Где ты это взял? Я удивлен. - person santiagobasulto; 02.02.2012

arrow_upward
1
arrow_downward

Это проще всего обобщить, поместив значения поиска в таблицу ...

INSERT INTO search SELECT 1
         UNION ALL SELECT 2
         UNION ALL SELECT 3

SELECT
  camera_features.camera_id
FROM
  camera_features
INNER JOIN
  search
    ON search.id = camera_features.feature_id
GROUP BY
  camera_features.camera_id
HAVING
  COUNT(DISTINCT camera_features.feature_id) = (SELECT COUNT(DISTINCT id) FROM search)
person MatBailie    schedule 01.02.2012