Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Takashi Member Откуда: Сообщений: 9 |
Добрый день! есть две таблицы: 1) Таблица скидок [Id_param] - id скидки [Name] - название скидки CREATE TABLE Params ( [Id_param] smallint IDENTITY(1,1) not null, [Name] nvarchar(50) not null CONSTRAINT Id_params PRIMARY KEY CLUSTERED ( [Id_param] ASC ) ) INSERT Params (Name) values('Скидка Пенсионер') INSERT Params (Name) values('Скидка прекрасного дня') INSERT Params (Name) values('Скидка Счастливчик') INSERT Params (Name) values('Скидка здравствуй') 2)Таблица товаров [id_object] - id товара [id_param] - скидки [cdate] - дата действия скидки [value] цена товара CREATE TABLE objects_params ( [id_object] int IDENTITY(1,1) not NUll, [id_param] smallint NUll, [cdate] smalldatetime not NUll, [value] float not NUll )ON [PRIMARY] ALTER TABLE objects_params WITH CHECK ADD CONSTRAINT FK_Id_params FOREIGN KEY([Id_param]) REFERENCES Params ([Id_param]) GO SET IDENTITY_INSERT objects_params ON DECLARE @cnt INT = 0; DECLARE @datetime smalldatetime = '2018-01-01 00:00:00'; DECLARE @id_object INT = 1; WHILE @cnt < 50 BEGIN INSERT objects_params ([id_object], [id_param],[cdate],[value]) values ( floor(13*RAND()+1),case when floor(4*RAND()+1)>3 then Null else floor(4*RAND()+1) end ,@datetime,cast(floor(1000*RAND()+1)*RAND() as decimal(8,2)) ) SET @cnt = @cnt + 1; SET @datetime = DATEADD(day,cast(@datetime as int),1) set @id_object=@id_object+1 END; Нужно найти для всех товаров актуальные цены на заданный период дат. Актуальной ценой считается последняя полученная цена. Написал запрос, но как сделать для произвольных скидок, без case? SELECT [id_object] ,sum("Скидка Пенсионер") as "Скидка Пенсионер" ,sum("Скидка прекрасного дня") as "Скидка прекрасного дня" ,sum("Скидка Счастливчик") as "Скидка Счастливчик" ,sum("Скидка здравствуй") as "Скидка здравствуй" FROM ( SELECT [id_object] ,case when name = 'Скидка Пенсионер' and MAX(s.cdate) over (partition by [id_object] )= s.cdate then value else 0 end as "Скидка Пенсионер" ,case when name = 'Скидка прекрасного дня' and MAX(s.cdate) over (partition by [id_object] )= s.cdate then value else 0 end as "Скидка прекрасного дня" ,case when name = 'Скидка Счастливчик'and MAX(s.cdate) over (partition by [id_object] )= s.cdate then value else 0 end as "Скидка Счастливчик" ,case when name = 'Скидка здравствуй' and MAX(s.cdate) over (partition by [id_object] )= s.cdate then value else 0 end as "Скидка здравствуй" FROM objects_params as s left JOIN Params as p on s.[id_param]=p.[id_param] WHERE cast(s.cdate as date) between '2018-01-02' and '2018-01-05' ) as s group by [id_object] Пытался использовать динамический sql c pivot, но то что я пишу не работает Помогите, пожалуйста! Помогите, пожалуйста, |
9 фев 18, 14:43 [21180210] Ответить | Цитировать Сообщить модератору |
Akina Member Откуда: Зеленоград, Москва, Россия Сообщений: 20960 |
FAQ: Выборка первой/последней записи в группах |
9 фев 18, 15:28 [21180401] Ответить | Цитировать Сообщить модератору |
Melkij Member Откуда: Санкт-Петербург Сообщений: 1246 |
Takashi, а вы mysql от ms sql не отличаете?
Сообщение было отредактировано: 9 фев 18, 16:56 |
|
9 фев 18, 16:44 [21180764] Ответить | Цитировать Сообщить модератору |
Дед-Папыхтет Member [заблокирован] Откуда: Сообщений: 2808 |
Takashi, Pivot |
11 фев 18, 08:10 [21182793] Ответить | Цитировать Сообщить модератору |
Takashi Member Откуда: Сообщений: 9 |
как я понимаю pivot поможет если знаешь какие поля. |
||
18 фев 18, 21:12 [21200268] Ответить | Цитировать Сообщить модератору |
Takashi Member Откуда: Сообщений: 9 |
попробовал реализовать динамический запрос, но выдаёт ошибку DECLARE @sql varchar (8000) DECLARE @id_param NVARCHAR(MAX)=N'1' DECLARE @name_param NVARCHAR(MAX)=N'(SELECT name FROM params where id_param='+ '@id_param)' DECLARE @data NVARCHAR(MAX)=N'2017-05-02' SET @sql= 'SELECT [id_object] ,sum("'+@name_param+'") as "'+@name_param+'" FROM ( SELECT [id_object] ,case when name = '+ @name_param + ' '+ 'and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value else 0 end as "'+@name_param+'" FROM objects_params as s left JOIN Params as p on s.[id_param]=p.[id_param] WHERE cast(s.cdate as date) <=' + @data + ') as s group by [id_object]' exec(@SQL) Сообщение 137, уровень 15, состояние 2, строка 153 Необходимо объявить скалярную переменную "@id_param". |
18 фев 18, 21:13 [21200269] Ответить | Цитировать Сообщить модератору |
iap Member Откуда: Москва Сообщений: 47049 |
DECLARE @name_param NVARCHAR(MAX)=N'(SELECT name FROM params where id_param='+@id_param+N')'Однако, дело не только в этом. В SUM() подзапрос вообще нельзя использовать. Зачем везде натыканы двойные кавычки, если там надо писать пары одинарных? Кстати, кое-где вообще пропущено. И у даты тоже. Это я только про синтаксис, если что. |
18 фев 18, 22:01 [21200353] Ответить | Цитировать Сообщить модератору |
Дед-Папыхтет Member [заблокирован] Откуда: Сообщений: 2808 |
Takashi, Добавь перед exec (@sql) команду print @sql И посмотри что за строку сформировал. Отлаживать учись, |
18 фев 18, 22:08 [21200362] Ответить | Цитировать Сообщить модератору |
Takashi Member Откуда: Сообщений: 9 |
Как тогда сделать сумму по полю которое получается в подзапросе? DECLARE @sql varchar (8000) DECLARE @id_param NVARCHAR(MAX)=N'1' DECLARE @name_param NVARCHAR(MAX)=N'(SELECT name FROM params where id_param='+@id_param+N')' DECLARE @data NVARCHAR(MAX)=N'(2017-05-02)' SET @sql= 'SELECT [id_object] ,sum("Размер") as "'+@name_param+'" FROM ( SELECT [id_object] ,case when name = '+ @name_param + ' '+ 'and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value else 0 end as '''+@name_param+''' FROM objects_params as s left JOIN Params as p on s.[id_param]=p.[id_param] WHERE cast(s.cdate as date) <=' + '''@data''' + ') as s group by [id_object]' exec(@SQL) |
||
18 фев 18, 22:24 [21200387] Ответить | Цитировать Сообщить модератору |
Дедушка Member Откуда: Город трёх революций Сообщений: 5114 |
Takashi, получите это ваше name_param не как кусок динамики, а как результат запроса перед динамикой DECLARE @name_param NVARCHAR(MAX)=(SELECT name FROM params where id_param=@id_param) |
18 фев 18, 22:31 [21200395] Ответить | Цитировать Сообщить модератору |
Takashi Member Откуда: Сообщений: 9 |
спасибо! сделал DECLARE @sql varchar (8000) DECLARE @id_param NVARCHAR(MAX)=N'1' DECLARE @name_param NVARCHAR(MAX)=(SELECT name FROM params where id_param=@id_param) DECLARE @data NVARCHAR(MAX)=('2017-05-02') SET @sql= 'SELECT [id_object] ,sum("'+@name_param+'") as "'+@name_param+'" FROM ( SELECT [id_object] ,case when name = '''+@name_param+''' and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value else 0 end as "'+@name_param+'" FROM objects_params as s left JOIN Params as p on s.[id_param]=p.[id_param] WHERE cast(s.cdate as date) <= '''+@data+''') as s group by [id_object]' exec(@SQL) |
||
18 фев 18, 22:48 [21200413] Ответить | Цитировать Сообщить модератору |
Takashi Member Откуда: Сообщений: 9 |
изначально я использовал запрос SELECT [id_object] ,sum("Размер") as "Размер" ,sum("Наличие фурнитуры") as "Наличие фурнитуры" ,sum("Наличие петель") as "Наличие петель" ,sum("Защита от влаги") as "Защита от влаги" FROM ( SELECT [id_object] ,case when name = 'Размер'and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value else 0 end as "Размер" ,case when name = 'Наличие фурнитуры' and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value else 0 end as "Наличие фурнитуры" ,case when name = 'Наличие петель'and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value else 0 end as "Наличие петель" ,case when name = 'Защита от влаги' and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value else 0 end as "Защита от влаги" FROM objects_params as s left JOIN Params as p on s.[id_param]=p.[id_param] WHERE cast(s.cdate as date) <= '2017-05-02' -- @data ) as s group by [id_object]; решил прибегнуть к динамическому запросу, но в итоге в каждом case имя параметра указывать всё равно придется, или есть другое решение? DECLARE @sql varchar (8000) DECLARE @id_param NVARCHAR(MAX)=N'1' DECLARE @name_param NVARCHAR(MAX)=(SELECT name FROM params where id_param=@id_param) DECLARE @data NVARCHAR(MAX)=('2017-05-02') SET @sql= 'SELECT [id_object] ,sum("'+@name_param+'") as "'+@name_param+'" FROM ( SELECT [id_object] ,case when name = '''+@name_param+''' and MAX(s.cdate) over (partition by [id_object],s.[id_param])= s.cdate then value else 0 end as "'+@name_param+'" FROM objects_params as s left JOIN Params as p on s.[id_param]=p.[id_param] WHERE cast(s.cdate as date) <= '''+@data+''') as s group by [id_object]' exec(@SQL) |
18 фев 18, 22:55 [21200421] Ответить | Цитировать Сообщить модератору |
Дедушка Member Откуда: Город трёх революций Сообщений: 5114 |
пример смотрите тут |
||
18 фев 18, 23:23 [21200458] Ответить | Цитировать Сообщить модератору |
Takashi Member Откуда: Сообщений: 9 |
Дедушка, спасибо за наглядный пример, удалось реализоватьDECLARE @columns NVARCHAR(MAX) DECLARE @sql NVARCHAR(MAX) DECLARE @data NVARCHAR(MAX)=('2017-05-02') SET @columns = N''; SELECT @columns += N', p.' + QUOTENAME(Name) FROM (SELECT name FROM Params) AS x; SET @sql = N' SELECT id_object, ' + STUFF(@columns, 1, 2, '') + ' FROM ( SELECT id_object ,p.[name] ,case when s.cdate = MAX(s.cdate) over (partition by [id_object],s.[id_param] ) then value else 0 end as [max] FROM objects_params as s left JOIN Params as p on s.[id_param]=p.[id_param] WHERE cast(s.cdate as date) <= '''+@data+''' ) AS j PIVOT ( max([max]) FOR Name IN (' + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ') ) AS p;'; PRINT @sql; EXEC sp_executesql @sql; |
19 фев 18, 21:28 [21203260] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |