Блог

Реляционная схема

Учитывая приведенную выше реляционную схему, я, похоже, не могу понять, как решить этот запрос:

8. Напишите SQL-запрос, чтобы найти названия фильмов, которые есть во всех магазинах (т. Е. Во всех запасах магазина). Предположим, что может быть любое количество хранилищ (т. Е. Вы не можете предположить определенное количество хранилищ). Ваш запрос также не может использовать COUNT() . (Подсказка: с помощью подзапросов нетрудно найти магазины, в которых нет определенного фильма.)

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

(для дальнейшего использования вы можете «помочь нам помочь вам», выполнив этот шаг для нас. Это также показывает, что вы немного подумали о том, с чем связана проблема)

 DECLARE @t_film TABLE
(   film_id INT)

DECLARE @t_store TABLE
(   store_id INT)

DECLARE @t_inventory TABLE
(   inv_id INT IDENTITY,
    store_id INT,
    film_id INT
)

 INSERT INTO @t_film
 VALUES (1),
        (2),
        (3),
        (4),
        (5),
        (6)

 INSERT INTO @t_store
 VALUES (1),
        (2),
        (3),
        (4)

 INSERT INTO @t_inventory
 VALUES (1,1),
        (1,3),
        (1,4),
        (1,5),
        (2,1),
        (2,4),
        (2,5),
        (3,1),
        (3,2),
        (3,3),
        (3,5),
        (4,1),
        (4,2),
        (4,3),
        (4,4),
        (1,6),
        (2,6),
        (3,6),
        (4,6)
-- Every store has films 1 amp; 6
  

Теперь давайте начнем с подсказки, которую они вам дают, и возьмем ее оттуда.
«используйте подзапросы, чтобы найти магазины, в которых нет определенного фильма».

 -- First create a list of every store as if it had every film
SELECT *
  FROM @t_film f
 CROSS JOIN @t_store s
--Then compare that to the actual inventory of each store
  LEFT JOIN @t_inventory i ON f.film_id = i.film_id
                          AND s.store_id = i.store_id
--Then filter that list to only the films which dont appear in a particular store.
 WHERE i.inv_id IS NULL
  

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

 SELECT *
  FROM @t_film r
 WHERE film_id NOT IN (SELECT f.film_id
                         FROM @t_film f
                        CROSS JOIN @t_store s
                         LEFT JOIN @t_inventory i ON f.film_id = i.film_id
                                                 AND s.store_id = i.store_id
                        WHERE i.inv_id IS NULL)
  

Вероятно, есть более элегантный способ сделать это, но я думаю, что этот подход проще всего следовать логике. Например, вместо использования NOT IN вы можете использовать другой LEFT JOIN с нулевой проверкой для достижения того же результата.

 SELECT r.*
  FROM @t_film r -- result
  LEFT JOIN (SELECT f.film_id
               FROM @t_film f
              CROSS JOIN @t_store s
               LEFT JOIN @t_inventory i ON f.film_id = i.film_id
                                       AND s.store_id = i.store_id
              WHERE i.inv_id IS NULL) inv ON r.film_id = inv.film_id
 WHERE inv.film_id IS NULL
  

Дело в том, чтобы понять логику. Оттуда вы можете найти любое количество решений.