求一个数据分行的sql

[jx_401]

一组数据 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语句 谢谢啦

[jx_401]

等高手中~~~~

[gw6328]

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
*/

[gw6328]

@tx无用

[DataBox-MDX]

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 d

drop 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)
*/

[中国风]

use Tempdb
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
*/

[chtzhking]

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;
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

[jx_401]

谢谢大家了