根据条件拆分行


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_startterm_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。

相关内容

  • 没有找到相关文章

最新更新