Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Stephov Member Откуда: Armenia Сообщений: 22 |
Всем привет, Имеются две таблицы: Ingredients и Recipes. Ingredients: CREATE TABLE [dbo].[Ingredients] ( [Ingredient_ID] INT IDENTITY (1, 1) NOT NULL, [Ingredient_Description] NVARCHAR (100) NULL); В ней описаны все имеющиеся ингредиенты. Сам ингредиент может являться как сырьем, так и переработанным продуктом. Например:
Recipes: CREATE TABLE [dbo].[Recipes] ( [Row_ID] INT IDENTITY (1, 1) NOT NULL, [Produced_Ingredient_ID] INT DEFAULT ((0)) NOT NULL, [Needed_Ingredient_ID] INT DEFAULT ((0)) NOT NULL, [Needed_Quantity] FLOAT (53) DEFAULT ((0)) NOT NULL); В этой таблице указаны ингредиенты для получения каждого продукта. Например:
Из пшеницы и сои получается коровий корм, он перерабатывается в молоко, а и из него - масло, сыр и сливки. Приведена общая схема, без учета единиц измерения, цен и более детальных данных. Вопрос в следующем: возможно ли при помощи одного запроса вывести все ингредиенты, используемые в цепочке для получения, например, масла? То есть, SELECT * FROM Recipes R INNER JOIN Ingredients I ON R.Needed_Ingredient_ID = I.Ingredient_ID WHERE R.Produced_Ingredient_ID = 5выдаст только последний шаг. То есть, сколько молока нужно для масла. А из чего состоит само масло и так далее - до первичного продукта - это получить и не получается. Нужно все Needed_Ingredient_ID связать с Produced_Ingredient_ID предыдущего продукта. Видимо нужно использовать рекурсию и UNION-ами собрать все строки, но у меня такого опыта нет, поэтому решил обратиться в форум. |
||||||||||||||||||||||||||||||||||||||||||||||
14 апр 16, 01:21 [19054104] Ответить | Цитировать Сообщить модератору |
alexeyvg Member Откуда: Moscow Сообщений: 31783 |
Ну, или можно собирать циклом во временную таблицу. |
||
14 апр 16, 01:31 [19054108] Ответить | Цитировать Сообщить модератору |
Дедушка Member Откуда: Город трёх революций Сообщений: 5114 |
Stephov, если для вас подобные запросы не разовые, то я бы подумал о хранении именно цепочек (без рекурсии). |
14 апр 16, 10:48 [19054932] Ответить | Цитировать Сообщить модератору |
Stephov Member Откуда: Armenia Сообщений: 22 |
Запросы не разовые, и будут часто выполняться. Вы имеете в виду, что в данном случае СТЕ запрос сработает с более низкой производительностью, чем если данные собрать циклом? Но в таком случае, опять же возникает два вопроса: при заливке циклом, мне все равно надо использовать рекурсию, верно? Если же нет - я не знаю степень вложенности, чтоб используя UNION-ы собрать все данные.. DECLARE @Produced_Item AS INT = 5 SELECT * FROM Recipes WHERE Produced_Ingredient_ID = @Produced_Item UNION ALL SELECT * FROM Recipes WHERE Produced_Ingredient_ID IN (SELECT Needed_Ingredient_ID FROM Recipes WHERE Produced_Ingredient_ID = @Produced_Item) UNION ALL ... Если же в составе одного продукта имеется иной переработанный продукт - так вообще все смешивается, не пойму что делать.. |
14 апр 16, 11:44 [19055389] Ответить | Цитировать Сообщить модератору |
iljy Member Откуда: Сообщений: 8711 |
Stephov, нет, он имеет ввиду, что запросы с рекурсией могут иметь низкую производительность и лучше их выполнить один раз, сохранив результат, и дальше использовать его. Это имеет смысл, если исходная таблица меняется редко и на нее можно повесить триггер, синхронизирующий изменения. |
14 апр 16, 11:47 [19055418] Ответить | Цитировать Сообщить модератору |
alexeyvg Member Откуда: Moscow Сообщений: 31783 |
Если запросы для таких цепочек массовые, можно хранить данные уже в развёрнутом виде. Например, в виде хранения цепочки ИД, или используя "дерево Селко" |
||
14 апр 16, 12:30 [19055777] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8335 |
А именно типа данных hierarchyid. |
||
14 апр 16, 13:19 [19056183] Ответить | Цитировать Сообщить модератору |
Stephov Member Откуда: Armenia Сообщений: 22 |
Решил задачу с использованием CTE. Поскольку таблица небольшая, работает очень быстро.DECLARE @ProductID AS INT = 5; WITH FullList ( Ingredient_ID, Row_ID, Produced_Ingredient_ID, Needed_Ingredient_ID, Needed_Quantity ) AS ( SELECT I.Ingredient_ID , R.Row_ID , R.Produced_Ingredient_ID , R.Needed_Ingredient_ID , R.Needed_Quantity FROM Recipes R INNER JOIN Ingredients I ON R.Needed_Ingredient_ID = I.Ingredient_ID WHERE R.Produced_Ingredient_ID = @ProductID UNION ALL SELECT FL.Ingredient_ID , R.Row_ID , R.Produced_Ingredient_ID , R.Needed_Ingredient_ID , R.Needed_Quantity FROM Recipes R INNER JOIN FullList FL ON R.Produced_Ingredient_ID = FL.Needed_Ingredient_ID ) SELECT * FROM FullList; |
15 апр 16, 10:04 [19060178] Ответить | Цитировать Сообщить модератору |
alexeyvg Member Откуда: Moscow Сообщений: 31783 |
Например, в товарном справочнике, в дереве классификаторов товаров, мы использовали (давным давно это было) дерево Целко, потому что обращений было сотни/тыщи в секунду, а табличка маленькая, обновлялась редко, так что накладными расходами на такую структуру можно пренебречь, а вот расходы на циклы в CTE были большие. |
||
15 апр 16, 11:19 [19060689] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |