Рекурсивный CTE и сводка SQL Server не работают вместе

Почему этот SQL Fiddle не работает?

Полный сценарий скопирован:

create table tbl (
  id int,
  month varchar(9),
  value float);
insert tbl values
(1,'Jan',0.12),
(1,'Feb',0.36),
(1,'Mar',0.72),
(2,'Mar',0.11),
(2,'Apr',0.12),
(2,'May',0.36);

declare @tbl table (
  id int,
  number int,
  month varchar(9),
  value float);
insert @tbl
select id.id, Months.Number, Months.Name, t.value
from (values(1,'Jan'),
            (2,'Feb'),
            (3,'Mar'),
            (4,'Apr'),
            (5,'May'),
            (6,'Jun')) Months(Number,Name)
cross join (select distinct id from tbl) id
left join tbl t on t.month = Months.name and t.id=id.id;

;with cte as (
  select id,Number,month,isnull(Value,0.0)value
  from @tbl
  where Number=1
  union all
  select cte.id,cte.Number+1,cte.month,isnull(t.value,cte.Value)
  from cte
  join @tbl t on t.id=cte.id and t.number=cte.number+1
)
/*update t
set value=cte.value
from @tbl t
join cte on t.id=cte.id and t.number=cte.number;*/

select id, Jan,Feb,Mar,Apr,May,Jun
from (select id,month,value from /*@tbl*/ cte) p
pivot (max(value) for month in (Jan,Feb,Mar,Apr,May,Jun)) v;

Ожидаемый результат:

ID  JAN FEB MAR APR MAY JUN
1   0.12    0.36    0.72    0.72    0.72    0.72
2   0   0   0.11    0.12    0.36    0.36

Фактический результат:

ID  JAN FEB MAR APR MAY JUN
1   0.72    (null)  (null)  (null)  (null)  (null)
2   0.36    (null)  (null)  (null)  (null)  (null)

Если вы раскомментируете закомментированный код, он сработает. Однако, если вы выберете из CTE напрямую SELECT * FROM CTE, будут показаны те же значения, что и в @tbl после оператора UPDATE.

Некоторое время назад я потратил время на анализ CTE + ROW_NUMBER(), но надеялся, что кто-нибудь объяснит это.


sql-server tsql pivot common-table-expression recursive-query
person RichardTheKiwi    schedule 03.10.2012    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Я не получаю тех же результатов от CTE, что и от @tbl. Для CTE все месяцы JAN. Если вы измените определение CTE следующим образом:

;with cte as (
  select id,Number,month,isnull(Value,0.0)value
  from @tbl
  where Number=1
  union all
  select cte.id,cte.Number+1,t.month /*here there was cte.month*/,
         isnull(t.value,cte.Value) 
  from cte
  join @tbl t on t.id=cte.id and t.number=cte.number+1
)

Тогда я получаю те же результаты.

person Lamak    schedule 03.10.2012
comment
Классический ПЕБКАК. Слишком долго смотрел на это, и оно превратилось в звездные поля... спасибо за помощь. К вашему сведению, этот запрос использовался здесь stackoverflow.com/a/12716649/573261, и я думал, что обнаружил еще одну функцию CTE. - person RichardTheKiwi; 04.10.2012
comment
@RichardTheKiwi - рад, что смог помочь - person Lamak; 04.10.2012