Блог

Проблема

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

Вот мой пересказ проблемы:

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

Сделайте следующее:

1. Напишите псевдо-SQL-код, создающий таблицы, необходимые для моделирования вышеперечисленного.
2. Напишите запрос, который вернет записи для типов шляп, принадлежащих по крайней мере к 5 общедоступным категориям.

Моя Попытка

Поскольку основная часть моего опыта связана с SQLite, я рассмотрел это с точки зрения SQLite и написал об этом в комментарии в своем ответе.

Что касается пункта (1) выше, я создал три таблицы: Hat , Category и BelongsToCategory , причем последняя является объединяющей таблицей для первых двух. Я сделал public столбец Category таблицы INTEGER типа: 0 для false, 1 для true.

Что касается пункта (2), то это мой запрос:

 SELECT *
FROM Hat
WHERE
    (SELECT COUNT(*)
     FROM BelongsToCategory
         JOIN Category ON Category.code = BelongsToCategory.category
         WHERE (BelongsToCategory.hat = Hat.code) AND (Category.public = 1))
    >= 5
 

Проверяю Свою Попытку

Я проверил свою попытку следующим образом:

• Я создал свой собственный файл .db.
• Я открыл указанный файл с помощью браузера базы данных для SQLite.
• Я создал свои таблицы, как описано выше.
• Я добавил данные в эти таблицы следующим образом:
  • Я добавил в таблицу три новые записи Hat : hat1, hat2 и hat3.
  • Я добавил в таблицу шесть новых записей Category : cat1 — cat6, где cat1 — cat5 были общедоступными, а cat6 был частным.
  • Я добавил записи в BelongsToCategory таблицу, так что hat1 принадлежал к категориям cat1 — cat5, hat2 принадлежал только cat1, а hat3 принадлежал к категориям cat1 — cat4, а также cat6.
• Я запустил приведенный выше SQL-код.

Когда я запустил приведенный выше SQL-код, он вернул запись для hat1. Работа сделана! Не так быстро… Когда я попытался немного поиграть с ним, изменив 5 на 4, или 3, 2 и т. Д., Я продолжал получать запись для hat1, и ничего больше. Только когда я добрался до 1, начала появляться шляпа 3, и я никогда не видел шляпу 2.

Что здесь происходит? Что-то не так с моим запросом? Или это ошибка в браузере SQLite?

Что я хочу знать

• Является ли приведенный выше SQL — запрос правильным?
• Как это можно было бы улучшить?

Я не верю, что с вашим запросом что-то не так (я протестировал его, и он работает так, как ожидалось). Поэтому я полагаю, что вы, возможно, вставляете данные не так, как ожидалось.

Если вы добавите подзапрос подсчета в качестве дополнительного столбца к основному запросу и используете >= 0, вы сможете просмотреть подсчеты и посмотреть, что происходит с точки зрения подсчета

напр. :-

 SELECT *, (SELECT COUNT(*)
     FROM BelongsToCategory
         JOIN Category ON Category.code = BelongsToCategory.category
         WHERE (BelongsToCategory.hat = Hat.code) AND (Category.public = true)) AS cat_count
FROM Hat
WHERE
    (SELECT COUNT(*)
     FROM BelongsToCategory
         JOIN Category ON Category.code = BelongsToCategory.category
         WHERE (BelongsToCategory.hat = Hat.code) AND (Category.public = true))
    >= 0
;
 

Для проверки вышеизложенного было использовано следующее :-

 PRAGMA foreign_keys = ON;
DROP TABLE IF EXISTS BelongsToCategory;
DROP TABLE IF EXISTS Hat;
DROP TABLE IF EXISTS Category;
CREATE TABLE IF NOT EXISTS Hat (code INTEGER PRIMARY KEY, hattype TEXT, hatprice REAL, hatsize REAL, hatetc TEXT);
CREATE TABLE IF NOT EXISTS Category (code INTEGER PRIMARY KEY, category TEXT, public INTEGER);
CREATE TABLE IF NOT EXISTS BelongsToCategory (
    hat INTEGER REFERENCES Hat(code) ON DELETE CASCADE ON UPDATE CASCADE
    , category INTEGER REFERENCES Category(code) ON DELETE CASCADE ON UPDATE CASCADE
    , PRIMARY KEY(hat,category)
    )   
;

INSERT INTO Category VALUES(1,'cat1',true),(2,'cat2',true),(3,'cat3',true),(4,'cat4',true),(5,'cat5',true),(6,'cat6',false);
INSERT INTO Hat VALUES(1,'hat1',100,7,'other'),(2,'hat2',90,6,'other'),(3,'hat3',95,5,'other'),(4,'hat4',110,8,'other'),(5,'hat5',120,8,'other');
INSERT INTO BelongsToCategory VALUES
    (1,1),(1,2),(1,3),(1,4),(1,5),(1,6)
    ,(2,1)
    ,(3,1),(3,2),(3,3),(3,4)
    ,(5,6),(5,1)
;

SELECT *, (SELECT COUNT(*)
     FROM BelongsToCategory
         JOIN Category ON Category.code = BelongsToCategory.category
         WHERE (BelongsToCategory.hat = Hat.code) AND (Category.public = true)) AS cat_count
FROM Hat
WHERE
    (SELECT COUNT(*)
     FROM BelongsToCategory
         JOIN Category ON Category.code = BelongsToCategory.category
         WHERE (BelongsToCategory.hat = Hat.code) AND (Category.public = true))
    >= 0
;
 

Результат вышесказанного (с использованием >=0) :-

(используя >=5) :-

(используя >=4) :-