ID term
1 12-60
2 36-48
3 12-96
期望:-当范围不在12-96时,应将行拆分为12的倍数。结果应该是
1 12
1 24
1 36
1 48
1 60
2 36
2 48
3 null
基本上,您可以为此使用递归CTE。这有点复杂,因为除了一项,你要对所有项都这样做。但是:
with tt as (
select id, term, try_convert(int, left(term, charindex('-', term) - 1)) as term_start,
try_convert(int, stuff(term, 1, charindex('-', term), '')) as term_end
from t
),
cte as (
select id, term_start, term_end
from tt
where term <> '12-96'
union all
select id, term_start + 12, term_end
from cte
where term_start < term_end
)
select id, term_start
from cte
union all
select id, null
from t
where term = '12-96'
order by id, term_start;
这是一个db<>小提琴
假设列被分成term_start
和term_end
,您可以使用递归CTE。例如:
with
n as (
select id, term_start, term_end
from t
where term_start <> 12 or term_end <> 96
union all
select id, term_start + 12, term_end
from n
where term_start + 12 <= term_end
)
select id, term_start from n
union all select id, null from t where term_start = 12 and term_end = 96
order by id, term_start
结果:
id term_start
--- ----------
1 12
1 24
1 36
1 48
1 60
2 36
2 48
3 null
参见运行示例:db<>fiddle。