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

Откуда: Москва
Сообщений: 598
Есть таблица:
create table #Table (mes INTEGER, Artikul NVARCHAR(10),Data datetime,kolz integer,kolP integer)
go
INSERT #Table  VALUES(1, N'01Б714545','2009-09-21',45,30)
INSERT #Table  VALUES(1, N'01С712245','2009-09-22',32,64)
INSERT #Table  VALUES(3, N'01К565423',Null,100,20)
INSERT #Table  VALUES(5, N'01К565423',Null,50,0)
INSERT #Table  VALUES(4, N'01Б710030','2009-10-03',150,200)
INSERT #Table  VALUES(6, N'01Б710030',Null,150,0)
INSERT #Table  VALUES(7, N'01Б710030',Null,20,0)

Нужно получить такую таблицу:

mesArtikuldatakolzkolpraznica
101Б71454521/9/20094530-15
101С71224522/9/2009326432
301К565423null10020-80
501к565423null500-50
401Б7100303/10/200915020050
601Б710030null15050-100
701Б710030null200-20


Т.е. создать в таблице ещё один столбец последний. туда писать разницу kolp-kolz, Если в таблице есть несколько подряд одинаковых артикулов, и разница в первой строке последнего столбца положительная, то эту разницу перенести на следующую строку в столбец kolp и записать kolp+raznica предыдущей строки. и в разницу уже следующей строки писать уже kolp-kolz, и если она опять положительная то повторить операцию. А если отрицательная то всё оставляем как есть.
7 окт 09, 11:55    [7752407]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
minya13_85
Если в таблице есть несколько подряд одинаковых артикулов, и разница в первой строке последнего столбца положительная,
то эту разницу перенести на следующую строку
Первая начиная откуда?
Следующая в каком порядке?
7 окт 09, 12:01    [7752444]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
Паганель
Первая начиная откуда?
Следующая в каком порядке?
первая упорядоченная по месяцу, где Data is not null там считается разница. Следующая следующая за ней, с Data is null
7 окт 09, 12:06    [7752486]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
;with numbered as (
   select mes, Artikul, Data, kolz, kolP
         ,row_number() over(order by Artikul, mes) as rn
     from #Table
), cte as (
   select rn, mes, Artikul, Data, kolz, kolP, kolP - kolz as raznica
     from numbered
    where rn = 1
   union all
   select n.rn, n.mes, n.Artikul, n.Data, n.kolz, t.kolP, t.kolP - n.kolz as raznica
     from numbered as n
     join cte as c on c.rn + 1  = n.rn
    cross apply (select case when c.Artikul = n.Artikul and c.raznica > 0 then c.raznica
                             else n.kolP
                         end as kolP) as t
)
select mes, Artikul, Data, kolz, kolP, raznica 
  from cte
 order by case when mes = 4 then 5
               when mes = 5 then 4
               else mes
           end
         ,Artikul

mes         Artikul    Data                    kolz        kolP        raznica
----------- ---------- ----------------------- ----------- ----------- -----------
1           01Б714545  2009-09-21 00:00:00.000 45          30          -15
1           01С712245  2009-09-22 00:00:00.000 32          64          32
3           01К565423  NULL                    100         20          -80
5           01К565423  NULL                    50          0           -50
4           01Б710030  2009-10-03 00:00:00.000 150         200         50
6           01Б710030  NULL                    150         50          -100
7           01Б710030  NULL                    20          0           -20

(7 row(s) affected)
7 окт 09, 12:25    [7752639]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
ещё вариант:
;
with q (mes, Artikul, Data, kolz, kolP, id, dif)
as
(
select
*
,row_number() over(order by Artikul,mes)
,kolP-kolz
from
@Table t
)

select
t1.mes
,t1.Artikul
,t1.Data
,t1.kolz
,case when t1.Artikul=t2.Artikul and t2.dif>0 then t1.kolP+t2.dif else t1.kolP end as kolP
,case when t1.Artikul=t2.Artikul and t2.dif>0 then t1.kolP+t2.dif-t1.kolz else t1.dif end as raznica
from
q t1
left join q t2 on t2.id=t1.id-1

mes         Artikul    Data                    kolz        kolP        raznica
----------- ---------- ----------------------- ----------- ----------- -----------
4           01Б710030  2009-10-03 00:00:00.000 150         200         50
6           01Б710030  NULL                    150         50          -100
7           01Б710030  NULL                    20          0           -20
1           01Б714545  2009-09-21 00:00:00.000 45          30          -15
3           01К565423  NULL                    100         20          -80
5           01К565423  NULL                    50          0           -50
1           01С712245  2009-09-22 00:00:00.000 32          64          32



--------------------------------------------------------------
Дьявол кроется в деталях.
7 окт 09, 12:35    [7752723]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
Дедушка
ещё вариант:
;
with q (mes, Artikul, Data, kolz, kolP, id, dif)
as
(
select
*
,row_number() over(order by Artikul,mes)
,kolP-kolz
from
@Table t
)

select
t1.mes
,t1.Artikul
,t1.Data
,t1.kolz
,case when t1.Artikul=t2.Artikul and t2.dif>0 then t1.kolP+t2.dif else t1.kolP end as kolP
,case when t1.Artikul=t2.Artikul and t2.dif>0 then t1.kolP+t2.dif-t1.kolz else t1.dif end as raznica
from
q t1
left join q t2 on t2.id=t1.id-1

mes         Artikul    Data                    kolz        kolP        raznica
----------- ---------- ----------------------- ----------- ----------- -----------
4           01Б710030  2009-10-03 00:00:00.000 150         200         50
6           01Б710030  NULL                    150         50          -100
7           01Б710030  NULL                    20          0           -20
1           01Б714545  2009-09-21 00:00:00.000 45          30          -15
3           01К565423  NULL                    100         20          -80
5           01К565423  NULL                    50          0           -50
1           01С712245  2009-09-22 00:00:00.000 32          64          32
Не срабатывает на другом примере.. Если в таблице #Table изменить в 6 месяце 0 на 150 т.е.

create table #Table (mes INTEGER, Artikul NVARCHAR(10),Data datetime,kolz integer,kolP integer)
go
INSERT #Table  VALUES(1, N'01Б714545','2009-09-21',45,30)
INSERT #Table  VALUES(1, N'01С712245','2009-09-22',32,64)
INSERT #Table  VALUES(3, N'01К565423',Null,100,20)
INSERT #Table  VALUES(5, N'01К565423',Null,50,0)
INSERT #Table  VALUES(4, N'01Б710030','2009-10-03',150,200)
INSERT #Table  VALUES(6, N'01Б710030',Null,150,150)
INSERT #Table  VALUES(7, N'01Б710030',Null,20,0)

он правильно, для артикула 01Б710030, 50 штук перекидывает на 150 сверху в следующей строке, а потом опять появляется 50 разница, но запрос почему-то не перекидывает на след. строку где kolz=20 для артикула 01Б710030
7 окт 09, 13:59    [7753457]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
да, мой косяк, не заметил, что вам нужны итерации для всех строк одного артикула.
7 окт 09, 16:23    [7754464]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
Дедушка
да, мой косяк, не заметил, что вам нужны итерации для всех строк одного артикула.
а как будет по-другому?
7 окт 09, 17:35    [7754893]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5111
minya13_85
Дедушка
да, мой косяк, не заметил, что вам нужны итерации для всех строк одного артикула.
а как будет по-другому?

вариант от Паганель что тоже плох :) ?
7 окт 09, 17:37    [7754905]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
Дедушка
вариант от Паганель что тоже плох :) ?


он выдает ошибку
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
8 окт 09, 09:58    [7756904]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
minya13_85,

Это дефолтное ограничение для рекурсивных CTE. Используйте хинт OPTION (MAXRECURSION <N>);.

Сообщение было отредактировано: 8 окт 09, 10:00
8 окт 09, 10:00    [7756914]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
pkarklin
minya13_85,

Это дефолтное ограничение для рекурсивных CTE. Используйте хинт OPTION (MAXRECURSION <N>);.
Не понял..(( Вы можете посмотреть вариант Паганеля? Там вместо Cte поставить hint option (MAXRECURSION <N>) ? или куда?
8 окт 09, 10:07    [7756951]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
minya13_85
Не понял..(( Вы можете посмотреть вариант Паганеля? Там вместо Cte поставить hint option (MAXRECURSION <N>) ? или куда?


Допишите последней строкой.
8 окт 09, 10:08    [7756957]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
pkarklin

Допишите последней строкой.

пишу в последней строке
hint options (MAXRECURSION <150>)
не знает слова hint и options
8 окт 09, 10:13    [7756989]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
minya13_85
pkarklin

Допишите последней строкой.

пишу в последней строке
hint options (MAXRECURSION <150>)
не знает слова hint и options


Мдя... С документацией грех, конечно, знакомиться...

...
OPTION (MAXRECURSION 1000);
8 окт 09, 10:15    [7757001]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
WITH общее_табличное_выражение (Transact-SQL)
см. пример Ж. Использование подсказки MAXRECURSION
8 окт 09, 10:19    [7757023]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
Паганель
WITH общее_табличное_выражение (Transact-SQL)
см. пример Ж. Использование подсказки MAXRECURSION
Я поставил максимум 32767, Интересно, а если у меня в запросе, кол-во уровней рекурсий превысит 32767 тогда как? ))
8 окт 09, 11:18    [7757432]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
minya13_85
если у меня в запросе, кол-во уровней рекурсий превысит 32767 тогда как? ))
Если для Вашей задачи такая ситуация является нормой, тогда придется отказаться от cte и сделать то же самое (скорее всего) курсором
Но тут я Вам ничем не подскажу ибо не умею
8 окт 09, 11:28    [7757522]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
Паганель
minya13_85
если у меня в запросе, кол-во уровней рекурсий превысит 32767 тогда как? ))
Если для Вашей задачи такая ситуация является нормой, тогда придется отказаться от cte и сделать то же самое (скорее всего) курсором
Но тут я Вам ничем не подскажу ибо не умею
ну вот и я о том же. Я пытался решить данную задачу курсором но не вышло..Сама глобальная задача вот ссылка на задачу
8 окт 09, 12:03    [7757780]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
iljy
Member

Откуда:
Сообщений: 8711
minya13_85
Паганель
WITH общее_табличное_выражение (Transact-SQL)
см. пример Ж. Использование подсказки MAXRECURSION
Я поставил максимум 32767, Интересно, а если у меня в запросе, кол-во уровней рекурсий превысит 32767 тогда как? ))



а прочитать документацию и поставить OPTION(MAXRESURSION 0) - религия не позволяет?
8 окт 09, 12:19    [7757896]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
ТАРАКАН
Member

Откуда:
Сообщений: 439
iljy
minya13_85
Паганель
WITH общее_табличное_выражение (Transact-SQL)
см. пример Ж. Использование подсказки MAXRECURSION
Я поставил максимум 32767, Интересно, а если у меня в запросе, кол-во уровней рекурсий превысит 32767 тогда как? ))



а прочитать документацию и поставить OPTION(MAXRESURSION 0) - религия не позволяет?

тож не сразу заметил :)
"при указании 0 не применяется никакого ограничения."
8 окт 09, 12:21    [7757912]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
aleks2
Guest
minya13_85
Паганель
WITH общее_табличное_выражение (Transact-SQL)
см. пример Ж. Использование подсказки MAXRECURSION
Я поставил максимум 32767, Интересно, а если у меня в запросе, кол-во уровней рекурсий превысит 32767 тогда как? ))


Ты лучше задумайся: с чего бы это Microsoft решил ограничить число рекурсий 100?
8 окт 09, 12:27    [7757954]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
aleks2

Ты лучше задумайся: с чего бы это Microsoft решил ограничить число рекурсий 100?

я думаю это по умолчанию..))))
8 окт 09, 12:33    [7757992]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
minya13_85,

Посмотрел Вашу задачу, это Вы пытаетесь написать механизм движения материалов, это нетривиальная задача, и ей мне кажется место в разделе работа:) потому что полноценный механизм будет стоить больших денег, и никто вам сдесь его нахаляву не распишет.
8 окт 09, 13:00    [7758206]     Ответить | Цитировать Сообщить модератору
 Re: Интересный запрос.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
WarAnt

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

Я не думаю что это должно быть в разделе Работа..)) это курсоры, но с ними мало кто дружит.((
8 окт 09, 15:45    [7759591]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить