关于数值拆分的问题
一个简单数据表结构:
id count
1 1
2 2
3 20
4 21
需要将count>10的做拆分 既
id count
1 1
2 2
3 10
3 10
4 10
4 10
4 1 这样的结果,求解!谢谢
[解决办法]
create table # (id int,CT INT)
insert #
select 1,1
union all select 2,2
union all select 3,24
union all select 4,41
;
declare @num int
select @num= 10;
;
with t1 as(
select a.id,CT/@num ctx from # a
WHERE CT/@num>0
),t2 as(
select a.* from # a where ct<@num
union all select id,@num from (
select ROW_NUMBER() OVER(partition BY a.id order by a.id)row,a.*
from t1 a, sys.sysobjects
)t where row<=ctx
union all select id,ct%@num from # a where ct>@num)
select * from t2 order by id,ct desc
drop table #