一组数据 id count
1 100
2 50
3 20
4 40要求count大于30的分成多行目标结果 id count
1 30
1 30
1 30
1 10
2 30
2 20
3 20
4 30
4 10求SQL语句 谢谢啦
1 100
2 50
3 20
4 40要求count大于30的分成多行目标结果 id count
1 30
1 30
1 30
1 10
2 30
2 20
3 20
4 30
4 10求SQL语句 谢谢啦
declare @t table(id int ,[count] int);
insert into @t select 1,100 union all
select 2,50 union all
select 3,20 union all
select 4,40;
declare @tx table(v int);
insert into @tx select 30 union all select 20 union all select 10;select x.id,case when y.number=0 then x.m else 30 end as count from
(select *,[count]%30 as m,([count]/30+case when[count]%30>0 then 1 else 0 end) as n from @t) x join
master..spt_values y on x.n>y.number where y.type='p'/*
id count
----------- -----------
1 10
1 30
1 30
1 30
2 20
2 30
3 20
4 10
4 30
*/
use master;
go
if OBJECT_ID('Test','u')is not null drop table Test
go
create table Test
(
a int,
b int
)
go
insert into Test
select 1 as a, 100 as b union all
select 2 as a, 50 as b union all
select 3 as a, 20 as b union all
select 4 as a, 40 as b
go
;with cte as
(
select *,
case when b>=30 then
REPLICATE('30,',b/30)+ltrim(b%30)
else ltrim(b) end as laststr
from Test
)
select aa.a,d.dd from
(
select *,cast('<V>'+REPLACE(laststr,',','</V><V>')+'</V>' as XML) as c from cte) as aa
outer apply
(
select C.value('.','nvarchar(50)') as dd
from aa.c.nodes('/V') as T(C)
)as ddrop table Test
/*
a dd
----------- --------------------------------------------------
1 30
1 30
1 30
1 10
2 30
2 20
3 20
4 30
4 10(9 row(s) affected)
*/
go
--> -->
declare @T table([id] int,[count] int)
Insert @T
select 1,100 union all
select 2,50 union all
select 3,20 union all
select 4,40
SELECT [id],[count]=CASE WHEN CEILING([count]*1.0/30)>number THEN 30 ELSE [count]%30 end
from @T AS a,master.dbo.spt_values AS b
WHERE b.type='P' AND b.number>0 AND CEILING([count]*1.0/30)>=b.number
/*
id count
1 30
1 30
1 30
1 10
2 30
2 20
3 20
4 30
4 10
*/
insert into @t select 1,100 union all
select 2,50 union all
select 3,20 union all
select 4,40;
select a.id,case when b.number<a.c then 30 else a.b end from (
select id,count%30 as b,count/30 as c from @t) as a join (select distinct number from master..spt_values where type='p') as b on a.c>=b.number