Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
Кетцаль
Member

Откуда:
Сообщений: 9
Здравствуйте, уважаемые гуру SQL. Необходима ваша помощь и советы.

У меня три таблица, которых необходимо преобразовать как это показано на рис.ниже.

Для этого я составила громоздкий СТАТИЧНЫЙ запрос:



SELECT
#Goods.*
, Shop1.QuantityShop1
, Shop2.QuantityShop2
, Shop3.QuantityShop3
, Shop4.QuantityShop4
, Shop5.QuantityShop5

FROM #Goods

--Присоединение столбца с количеством товаров Магазина№1
LEFT OUTER JOIN
(
SELECT
#Quantity.ShopsCode
, #Shops.ShopsDescription
, #Shops.ShopsID
, #Quantity.IDGoods
, SUM (#Quantity.GoodsQuantity) as QuantityShop1
FROM #Quantity
LEFT OUTER JOIN #Shops ON #Shops.ShopsCode=#Quantity.ShopsCode
WHERE #Shops.ShopsID=1
GROUP BY IDGoods, #Quantity.ShopsCode, #Shops.ShopsDescription, #Shops.ShopsID
) AS Shop1
ON Shop1.IDGoods=#Goods.IDGoods

--Присоединение столбца с количеством товаров Магазина№2
LEFT OUTER JOIN
(
SELECT
#Quantity.ShopsCode
, #Shops.ShopsDescription
, #Shops.ShopsID
, #Quantity.IDGoods
, SUM (#Quantity.GoodsQuantity) as QuantityShop2
FROM #Quantity
LEFT OUTER JOIN #Shops ON #Shops.ShopsCode=#Quantity.ShopsCode
WHERE #Shops.ShopsID=2
GROUP BY IDGoods, #Quantity.ShopsCode, #Shops.ShopsDescription, #Shops.ShopsID
) AS Shop2
ON Shop2.IDGoods=#Goods.IDGoods


--Присоединение столбца с количеством товаров Магазина№3
LEFT OUTER JOIN
(
SELECT
#Quantity.ShopsCode
, #Shops.ShopsDescription
, #Shops.ShopsID
, #Quantity.IDGoods
, SUM (#Quantity.GoodsQuantity) as QuantityShop3
FROM #Quantity
LEFT OUTER JOIN #Shops ON #Shops.ShopsCode=#Quantity.ShopsCode
WHERE #Shops.ShopsID=3
GROUP BY IDGoods, #Quantity.ShopsCode, #Shops.ShopsDescription, #Shops.ShopsID
) AS Shop3
ON Shop3.IDGoods=#Goods.IDGoods


--Присоединение столбца с количеством товаров Магазина№4
LEFT OUTER JOIN
(
SELECT
#Quantity.ShopsCode
, #Shops.ShopsDescription
, #Shops.ShopsID
, #Quantity.IDGoods
, SUM (#Quantity.GoodsQuantity) as QuantityShop4
FROM #Quantity
LEFT OUTER JOIN #Shops ON #Shops.ShopsCode=#Quantity.ShopsCode
WHERE #Shops.ShopsID=4
GROUP BY IDGoods, #Quantity.ShopsCode, #Shops.ShopsDescription, #Shops.ShopsID
) AS Shop4
ON Shop4.IDGoods=#Goods.IDGoods


--Присоединение столбца с количеством товаров Магазина№5
LEFT OUTER JOIN
(
SELECT
#Quantity.ShopsCode
, #Shops.ShopsDescription
, #Shops.ShopsID
, #Quantity.IDGoods
, SUM (#Quantity.GoodsQuantity) as QuantityShop5
FROM #Quantity
LEFT OUTER JOIN #Shops ON #Shops.ShopsCode=#Quantity.ShopsCode
WHERE #Shops.ShopsID=5
GROUP BY IDGoods, #Quantity.ShopsCode, #Shops.ShopsDescription, #Shops.ShopsID
) AS Shop5
ON Shop5.IDGoods=#Goods.IDGoods



ВОПРОС: КАК ПРЕВРАТИТЬ ЭТОТ ДЛИННЮЩИЙ ЗАПРОС В ДИНАМИЧЕСКИЙ С ВВОДОМ ЦИКЛА?

Чтобы в части с left outer join использовать переменную, вроде того:



LEFT OUTER JOIN
(
SELECT
#Quantity.ShopsCode
, #Shops.ShopsDescription
, #Shops.ShopsID
, #Quantity.IDGoods
, SUM (#Quantity.GoodsQuantity) as @ShopsDescription
FROM #Quantity
LEFT OUTER JOIN #Shops ON #Shops.ShopsCode=#Quantity.ShopsCode
WHERE #Shops.ShopsID=@i
GROUP BY IDGoods, #Quantity.ShopsCode, #Shops.ShopsDescription, #Shops.ShopsID
) AS @ShopsDescription
ON Shop5.IDGoods=#Goods.IDGoods


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


ПРИЛОЖЕНИЕ

Для желающих поработать с моим запросом прилагаю запросы для создания готовых временный таблиц:


Запрос для создания таблицы #Goods:
CREATE TABLE #Goods (
IDGoods BIGINT NULL
, GoodsDescription NVARCHAR(25) NULL
)

INSERT INTO #Goods (
IDGoods
, GoodsDescription
)
VALUES
(1032, 'Shirt')
, (1543, 'Dress')
, (1643, 'Shorts')
, (1873, 'Jacket')

SELECT * FROM #Goods







Запрос для создания таблицы #Shops:

CREATE TABLE #Shops (
ShopsID INT IDENTITY (1,1) NOT NULL
, ShopsCode BIGINT NULL
, ShopsDescription NVARCHAR(25) NULL
)

INSERT INTO #Shops (
ShopsCode
, ShopsDescription
)
VALUES
(90000, 'Shop1')
, (90004, 'Shop2')
, (90001, 'Shop3')
, (90009, 'Shop4')
, (90002, 'Shop5')

SELECT * FROM #Shops





Запрос для #Quantity
CREATE TABLE #Quantity (
ShopsCode BIGINT NULL
, IDGoods BIGINT NULL
, GoodsQuantity BIGINT NULL
)

INSERT INTO #Quantity (
ShopsCode
, IDGoods
, GoodsQuantity
)
VALUES
(90000,1032,1)
, (90000,1543,6)
, (90000,1643,5)
, (90000,1873,0)

, (90004,1032,0)
, (90004,1543, NULL)
, (90004,1643,2)
, (90004,1873,1)

, (90001,1032,10)
, (90001,1543,1)
, (90001,NULL,2)
, (90001,1873,8)

, (90009,1032,2)
, (90009,1543,3)
, (90009,1643,9)
, (90009,1873,1)

, (90002,1032,1)
, (90002,1543,1)
, (90002,1643,8)
, (90002,1873,1)


SELECT * FROM #Quantity

Заранее благодарю!

К сообщению приложен файл. Размер - 90Kb
1 сен 12, 13:02    [13099173]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
aleks2
Guest
Может сложить эти магазины в ОДНУ таблицу и не парить себе и людям мозг?
1 сен 12, 13:12    [13099190]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
Кетцаль
Member

Откуда:
Сообщений: 9
aleks2
Может сложить эти магазины в ОДНУ таблицу и не парить себе и людям мозг?


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

Столбцы магазинов (QuntityShopN) присоединяются друг к другу вместе на основе IDGoods. Поэтому собрать все магазины отдельно в одну таблицу, а затем присоединить к таблице #Goods (если я правильно вас поняла) не получается. Да и мне это не нужно. Требуется ОПТИМИЗАЦИЯ запроса на основе цикла и динамического SQL. Потому что таких магазинов у меня больше 20. И в дальнейшем, их кол-во будет увеличиваться. Согласитесь, всех их присоединять подобный статичным запросом, вытягивая его в колбаску - это неприлично даже для начинающего разработчика.
1 сен 12, 14:59    [13099403]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Кетцаль
Потому что таких магазинов у меня больше 20. И в дальнейшем, их кол-во будет увеличиваться. Согласитесь, всех их присоединять подобный статичным запросом, вытягивая его в колбаску - это неприлично даже для начинающего разработчика.
Вот именно. Поэтому и было предложено сделать табличку вида
create table dbo.Warehouse
(
 IDGoods int not null,
 ShopsID int not null,
 Quantity int not null,
 primary key (IDGoods, ShopsID)
);
А вы вместо этого придумали какой-то "динамический запрос с циклом"...
Лучше почитайте про нормальные формы.
1 сен 12, 15:35    [13099451]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
Homosum
Member

Откуда: Москва
Сообщений: 371
invm
Кетцаль
Потому что таких магазинов у меня больше 20. И в дальнейшем, их кол-во будет увеличиваться. Согласитесь, всех их присоединять подобный статичным запросом, вытягивая его в колбаску - это неприлично даже для начинающего разработчика.
Вот именно. Поэтому и было предложено сделать табличку вида
create table dbo.Warehouse
(
 IDGoods int not null,
 ShopsID int not null,
 Quantity int not null,
 primary key (IDGoods, ShopsID)
);
А вы вместо этого придумали какой-то "динамический запрос с циклом"...
Лучше почитайте про нормальные формы.


А может тописк стартеру как раз нужно именно не нормализованная форма.

Насколько я понял из запроса уже так и есть такого рода табличка. И ее нужно представить в не нормализованном виде.
1 сен 12, 15:43    [13099461]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Homosum
Насколько я понял из запроса уже так и есть такого рода табличка. И ее нужно представить в не нормализованном виде.
Ну если так, тогда нужно просто при создании нового магазина генерить триггером соответствующий view с pivot.
1 сен 12, 15:49    [13099468]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
вроде обычный
Guest
Кетцаль,

тут вроде обычный PIVOT нужен.
динамически тоже можно, но это (относительно) много кода и в результате получится "рукописный пивот". через group by с case.
1 сен 12, 16:18    [13099510]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
вроде обычный
Кетцаль,

тут вроде обычный PIVOT нужен.
динамически тоже можно, но это (относительно) много кода и в результате получится "рукописный пивот". через group by с case.
Кто мешает сделать динамический PIVOT без group by с case?
2 сен 12, 07:31    [13100671]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
имелось ввиду
Guest
Mind,

имелось ввиду "если хочется полностью руками без использования слова PIVOT"
2 сен 12, 10:45    [13100871]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
Кетцаль
Member

Откуда:
Сообщений: 9
Господа, я разве суть оператора PIVOT не в транспонировании таблицы? Ведь мне нужно всего лишь вывести название магазинов в заголовок БЕЗ транспанирования их значений.

Суть в чем? Мне нужно присоединить к ГлавнойТаблице еще 23 таблиц. Грубо говоря:

#ГлавнаяТаблица
LEFT OUTER JOIN #Taбл1
LEFT OUTER JOIN #Taбл2
LEFT OUTER JOIN #Taбл3
................................
LEFT OUTER JOIN #Taбл23


Меня интересует в первую очередь, как присоединять #Табл.1-#Табл.23 через более оптимальный способ (напр., через циклы)

Когда я применила цикл

@i=1
WHILE @i<=23
BEGIN
#ГлавнаяТаблица
LEFT OUTER JOIN #Taбл
where ID=@i
@i=@i+1
END


Результат выдает:

К сообщению приложен файл. Размер - 10Kb
2 сен 12, 11:52    [13100956]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
Зачем?
Guest
Кетцаль,
автор
Ведь мне нужно всего лишь вывести название магазинов в заголовок БЕЗ транспанирования их значений.

Зачем?
2 сен 12, 12:04    [13100980]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
много всего
Guest
Кетцаль,

сгенерированные вами вручную таблицы разве не являются аналогом транспонирования?
вы проделали много всего, в результате получился PIVOT и больше ничего.
2 сен 12, 13:58    [13101163]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
Кетцаль
Member

Откуда:
Сообщений: 9
Господа! Действительно, оператор PIVOT оказался очень удобен. Благодарю всех за совет.

Итак, я превратила таблицу #ShopQuantity в таблицу, кот. мне нужна (см.приложенный файл).

SELECT
IDGoods,
[Shop1], [Shop2], [Shop3], [Shop4] , [Shop5]
FROM
( SELECT IDGoods , GoodsQuantity, ShopsDescription FROM #ShopQuantity) AS Sourcetable
PIVOT ( AVG(GoodsQuantity) FOR
ShopsDescription in ([Shop1], [Shop2], [Shop3], [Shop4] , [Shop5])
) AS pivotable



ВОПРОС: если у меня 100 магазинов - как их присоединить ОПТИМАЛЬНЫМ методом? Т.е. мне нужно вместо [Shop1], [Shop2], и т.д. что-то вроде @Shop№

К сообщению приложен файл. Размер - 95Kb
3 сен 12, 11:14    [13103930]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
Кетцаль
Member

Откуда:
Сообщений: 9
ПРИЛОЖЕНИЕ

-- Запрос для таблицы #ShopQuantity, созданной из ранее объявленных таблиц #Shop и #Quantity
CREATE TABLE #ShopQuantity (
ShopsCode BIGINT NULL
, IDGoods BIGINT NULL
, GoodsQuantity BIGINT NULL
, ShopsDescription NVARCHAR(25) NULL)

INSERT INTO #ShopQuantity (
ShopsCode
, IDGoods
, GoodsQuantity
, ShopsDescription)

SELECT
#Quantity.*
, #Shops.ShopsDescription
FROM #Quantity
LEFT OUTER JOIN #Shops
ON #Shops.ShopsCode =#Quantity.ShopsCode

SELECT * FROM #ShopQuantity
3 сен 12, 11:18    [13103959]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
Кетцаль
Т.е. мне нужно вместо [Shop1], [Shop2], и т.д. что-то вроде @Shop№
Как то вы весь топик непонятно выражаетесь :-)

Что подразумевается под "присоединить магазин" - назвать имя колонки в результирующем наборе данных по содержимому переменной? А в поле ShopsDescription имена магазинов что, неправильные?
Если вам нужно эти имена заменить на что то друге по какому то алгоритму, то можно использовать строковые функции.
3 сен 12, 11:21    [13103989]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
а если так
Guest
go 

create function [dbo].ShopQuantity   ( @ShopsID BIGINT, @IDGoods BIGINT)
returns BIGINT
as begin
	return( isnull( ( select SUM (Quantity.GoodsQuantity) 
		           from Quantity
		             left join Shops on Shops.ShopsCode=Quantity.ShopsCode
		           where Shops.ShopsID= @ShopsID
		             and Quantity.IDGoods= @IDGoods
		       ) , 0)
	)
end

go

SELECT
Goods.*
, dbo.ShopQuantity( 1, Goods.IDGoods) as QuantityShop1
, dbo.ShopQuantity( 2, Goods.IDGoods) as QuantityShop2
, dbo.ShopQuantity( 3, Goods.IDGoods) as QuantityShop3
, dbo.ShopQuantity( 4, Goods.IDGoods) as QuantityShop4
, dbo.ShopQuantity( 5, Goods.IDGoods) as QuantityShop5
FROM Goods
3 сен 12, 13:18    [13105037]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
Кетцаль
Member

Откуда:
Сообщений: 9
Господа, спасибо за содействие. Прошу прощение, если несовсем ясно изъяснялась. Я наконец-таки составила нужный запрос через pivot и динамический запрос. Кому интересно, выкладываю:


DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ',[' + ShopsDescription + ']',
'[' + ShopsDescription + ']')
FROM #Shops
ORDER BY ShopsDescription


DECLARE @query NVARCHAR(4000)
SET @query = 'SELECT IDGoods, GoodsDescription,'+
@cols +'
FROM
( SELECT #Quantity.IDGoods , GoodsDescription, GoodsQuantity, ShopsDescription FROM #Quantity
LEFT OUTER JOIN #Shops ON #Shops.ShopsCode=#Quantity.ShopsCode
LEFT OUTER JOIN #Goods ON #Goods.IDGoods=#Quantity.IDGoods
) AS Sourcetable
PIVOT
(
AVG(GoodsQuantity)
FOR ShopsDescription IN
( '+
@cols +' )
) AS pvt'

EXECUTE(@query)


Особое спасибо тем, что открыл мне глаза на оператор pivot :))
4 сен 12, 16:53    [13112996]     Ответить | Цитировать Сообщить модератору
 Re: Как "обратить" длинный статичный запрос в динамичный с циклом.  [new]
Кетцаль
Member

Откуда:
Сообщений: 9
Результат:

(все данные для временный таблиц #Goods, #Shops и #Quantity можно найти в моих сообщениях выше)

К сообщению приложен файл. Размер - 8Kb
4 сен 12, 16:54    [13113019]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить