22,207
社区成员
发帖
与我相关
我的任务
分享
DECLARE @a TABLE(a INT,b DECIMAL(20,1))
INSERT @a SELECT 1, 2.8
union all SELECT 2, 3.6
UNION ALL SELECT 3, .3
SELECT a.a,CASE WHEN number<CEILING(a.b) THEN 1.0 ELSE a.b-FLOOR(a.b) END b FROM @a a ,MASTER..spt_values b
WHERE TYPE='P' AND number BETWEEN 1 AND CEILING (a.b)
ORDER BY a.a,number
--result
/*a b
----------- ------------------------
1 1.0
1 1.0
1 .8
2 1.0
2 1.0
2 1.0
2 .6
3 .3
(所影响的行数为 8 行)
*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a列] int,[b列] numeric(2,1))
insert [tb]
select 1,2.8 union all
select 2,3.6
go
-->测试开始
select a列,1[b列]from [tb] t, master..spt_values s
where s.number between 1 and floor([b列])
and s.type='p'
union all
select a列,[b列]-floor([b列]) from [tb]
order by a列,b列 desc
/*
a列 b列
----------- ---------------------------------------
1 1.0
1 1.0
1 0.8
2 1.0
2 1.0
2 1.0
2 0.6
(7 行受影响)
*/
while exists(select top 1 from tb where b>1)
begin
select top 1 @id=id,@a=a from tb where b>1
insert into tb(a,b) values(@a,1)
update tb set b=b-1 where id=@id
end