SQL: Расширение строки с помощью start / конец в отдельные строки
У меня есть такие записи:
start, end , total
830 , 1300, 5
1400, 1430, 2
, которые я хотел бы расширить до:
instance , total
830 , 5
831 , 5
832 , 5
...
1299 , 5
1300 , 5
1400 , 2
1401 , 2
...
1429 , 2
1430 , 2
Как я могу сделать это с помощью SQL в MSSQL 2005?
РЕДАКТИРОВАТЬ: спасибо всем, отличные ответы. Надо немного поработать. Я просто забыл сказать, что начало / конец действительно было временем, хранящимся как целое, поэтому с 0830 до 1300 должно идти до 0859, а затем до 0900. Я не могу ожидать, что вы, ребята, ответите на этот же вопрос, я обдумаю его. Еще раз спасибо
3 ответа
Использование CTE:
with number_cte(n) as
(select n from (select 0 n) m union all select n+1 n
from number_cte where n< 2400)
select start+n instance, total
from
datatable
join number_cte on start+n between start and [end]
where start+n - 100*floor((start+n)/100) between 0 and 59
order by 1
option (maxrecursion 2401)
(Увеличьте n <... и максимальное число рекурсий, если требуются диапазоны больше 2400)
Отредактировано для предотвращения включения недействительных значений экземпляра (т. е. значений времени, заканчивающихся между 60 и 99).
Это должно помочь:
create table input (start int, [end] int, total int)
insert input values (830, 1300, 5)
insert input values (1400, 1430, 2)
declare @output table (instance int, start int, [end] int, total int)
insert @output select start, start, [end], total from input
while @@rowcount > 0
insert @output
select
max(instance) + 1,
start,
[end],
total
from @output
group by
start,
[end],
total
having max(instance) < [end]
select instance, total from @output order by instance
Будет выведен тот же результат (без усечения), который вы описали в вопросе.
Там может быть какой-то необычный подход CTE, но я не думаю, что он сработает, поскольку вам понадобится неопределенное количество рекурсии.
Предполагая, что существует конечный максимум для ваших значений END, вы можете использовать таблицу чисел (измените 2000, которое я использовал как ваше максимальное значение END):
declare @Test table (
start int,
[end] int,
total int
)
insert into @Test
(start, [end], total)
select 830, 1300, 5
union
select 1400, 1430, 2
;WITH Nbrs ( n ) AS (
SELECT 1 UNION ALL
SELECT 1 + n FROM Nbrs WHERE n < 2000
)
select n.n, t.total
from @Test t
cross join Nbrs n
where n.n between t.start and t.[end]
option (MAXRECURSION 2000)