求个 有点难度的sql

Question

 一共11列，第一列唯一ID，其他是各种费用 charge1,.....charge10
 
 现在问题是，假如一行数据
 F101 , 250.5 , 10 , 50 , 68 , 85 , 92 , 60 , 800 ,200 ,100 一共应付 1715.5 元，但病人只交纳了1000元，现在想按charge1,charge2,charge3,charge4... 按顺序填充这1000元
 结果应该为
 F101 , 250.5 , 10 , 50 , 68 , 85 , 92 , 60 , 384.5 , 0 , 0   怎样写sql,郁闷中！急求
 

Answer 1

select charge1 = case when charge1<=1000 then charge1 else 1000 end
,charge2 = case when charge1<=1000 then 
             case when charge1+charge2<=1000 then charge2
                   else 1000-charge1
                   end
              else 0 end
,charge3 = case when charge1+charge2<=1000 then 
             case when charge1+charge2+charge3<=1000 then charge2
                   else 1000-charge1-charge2
                   end
              else 0 end
,charge4 = case when charge1+charge2+charge3<=1000 then 
             case when charge1+charge2+charge3+charge4<=1000 then charge2
                   else 1000-charge1-charge2-charge3
                   end
              else 0 end
...
from tab
where id='F101'

Answer 2

create table tb
(
uer varchar(10),
charing1 decimal(12,2),
charing2 decimal(12,2)
)
insert into tb
select 'F101',250.5,10
godeclare @i decimal(12,2)
set @i = 254
select uer,
(case when charing1 < @i then charing1 else @i end)charing1,
(case when charing1 < @i then 
case when charing1+charing2 < @i then charing2 else @i - charing1 end 
else 0 end)charing2
from tbdrop table tb/*
uer        charing1                                charing2
---------- --------------------------------------- ---------------------------------------
F101       250.50                                  3.50(1 行受影响)

Answer 3

高手就是高手
向高手
good good study!

Answer 4

方法有点笨，很久没写了，都不太会了，仅供参考：
CREATE TABLE #Charge
(
id INT IDENTITY,
charge_1 DECIMAL(10,2),
charge_2 DECIMAL(10,2),
charge_3 DECIMAL(10,2),
charge_4 DECIMAL(10,2),
charge_5 DECIMAL(10,2),
charge_6 DECIMAL(10,2),
charge_7 DECIMAL(10,2),
charge_8 DECIMAL(10,2),
charge_9 DECIMAL(10,2),
charge_10 DECIMAL(10,2)
)INSERT INTO #Charge 
SELECT 250.5 , 10 , 50 , 68 , 85 , 92 , 60 , 800 ,200 ,100SELECT * FROM #Charge;DECLARE @FuKuan DECIMAL
SET @FuKuan=1000SELECT 
 charge_1 = CASE  WHEN @FuKuan>=charge_1 
THEN charge_1 ELSE @FuKuan END 
,charge_2 = CASE  WHEN @FuKuan>=charge_1+ charge_2 
THEN charge_2 ELSE @FuKuan-charge_1 END 
,charge_3 = CASE  WHEN @FuKuan>=charge_1+ charge_2 + charge_3 
THEN charge_3 ELSE @FuKuan-charge_1 -charge_2 END 
,charge_4 = CASE  WHEN @FuKuan>=charge_1+ charge_2 + charge_3 + charge_4
THEN charge_4 ELSE @FuKuan-charge_1-charge_2-charge_3 END 
,charge_5 = CASE  WHEN @FuKuan>=charge_1+ charge_2+ charge_3 + charge_4  + charge_5
THEN charge_5 ELSE @FuKuan-charge_1-charge_2-charge_3-charge_4 END 
,charge_6 = CASE  WHEN @FuKuan>=charge_1+ charge_2+charge_3 + charge_4  + charge_5+ charge_6
THEN charge_6 ELSE @FuKuan-charge_1-charge_2-charge_3-charge_4-charge_5 END 
,charge_7 = CASE  WHEN @FuKuan>=charge_1+ charge_2 +charge_3 + charge_4  + charge_5+ charge_6+ charge_7
THEN charge_7 ELSE @FuKuan-charge_1-charge_2-charge_3-charge_4-charge_5-charge_6 END 
,charge_8 = CASE  WHEN @FuKuan>=charge_1+ charge_2+charge_3 + charge_4  + charge_5+ charge_6+ charge_7 + charge_8
THEN charge_8 ELSE @FuKuan-charge_1-charge_2-charge_3-charge_4-charge_5-charge_6 - charge_7 END 
,charge_9 = CASE  WHEN @FuKuan>=charge_1+ charge_2+charge_3 + charge_4  + charge_5+ charge_6+ charge_7 + charge_8 + charge_9
THEN charge_9 ELSE @FuKuan-charge_1-charge_2-charge_3-charge_4-charge_5-charge_6 - charge_7- charge_8 END 
,charge_10 = CASE  WHEN @FuKuan>=charge_1+ charge_2 +charge_3 + charge_4  + charge_5+ charge_6+ charge_7 + charge_8 + charge_9+ charge_10
THEN charge_10 ELSE @FuKuan-charge_1 -charge_2-charge_3-charge_4-charge_5-charge_6 - charge_7- charge_8 - charge_9 END 
FROM #Charge c
charge_1                                charge_2                                charge_3                                charge_4                                charge_5                                charge_6                                charge_7                                charge_8                                charge_9                                charge_10
--------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
250.50                                  10.00                                   50.00                                   68.00                                   85.00                                   92.00                                   60.00                                   384.50                                  -415.50                                 -615.50(1 row(s) affected)

Answer 5

除了case when 暂时想不到其他办法...

Answer 6

create table tb(id nvarchar(10),c1 decimal(10,2),c2 decimal(10,2),c3 decimal(10,2),c4 decimal(10,2),c5 decimal(10,2),c6 decimal(10,2),c7 decimal(10,2),c8 decimal(10,2),c9 decimal(10,2),c10 decimal(10,2))
insert into tb select 'F101',250.5,10,50,68,85,92,60,800,200,100
go
declare @je decimal(10,2)
set @je=1000
select ID,
(case when c1<@je then c1 when @je<=0 then 0 else @je end) c1,
(case when c1+c2<@je then c2 when @je-c1<=0 then 0 else @je-c1 end) c2,
(case when c1+c2+C3<@je then c3 when @je-c1-c2<=0 then 0 else @je-c1-c2 end) c3,
(case when c1+c2+c3+c4<@je then c4 when @je-c1-c2-c3<=0 then 0 else @je-c1-c2-c3 end) c4,
(case when c1+c2+c3+c4+c5<@je then c5 when @je-c1-c2-c3-c4<=0 then 0 else @je-c1-c2-c3-c4 end) c5,
(case when c1+c2+c3+c4+c5+c6<@je then c6 when @je-c1-c2-c3-c4-c5<=0 then 0 else @je-c1-c2-c3-c4-c5 end) c6,
(case when c1+c2+c3+c4+c5+c6+c7<@je then c7 when @je-c1-c2-c3-c4-c5-c6<=0 then 0 else @je-c1-c2-c3-c4-c5-c6 end) c7,
(case when c1+c2+c3+c4+c5+c6+c7+c8<@je then c8 when @je-c1-c2-c3-c4-c5-c6-c7<=0 then 0 else @je-c1-c2-c3-c4-c5-c6-c7 end) c8,
(case when c1+c2+c3+c4+c5+c6+c7+c8+c9<@je then c9 when @je-c1-c2-c3-c4-c5-c6-c7-c8<=0 then 0 else @je-c1-c2-c3-c4-c5-c6-c7-c8 end) c9,
(case when c1+c2+c3+c4+c5+c6+c7+c8+c9+c10<@je then c10 when @je-c1-c2-c3-c4-c5-c6-c7-c8-c9<=0 then 0 else @je-c1-c2-c3-c4-c5-c6-c7-c8-c9 end) c10
from tb
go
drop table tb
/*
ID         c1                                      c2                                      c3                                      c4                                      c5                                      c6                                      c7                                      c8                                      c9                                      c10
---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
F101       250.50                                  10.00                                   50.00                                   68.00                                   85.00                                   92.00                                   60.00                                   384.50                                  0.00                                    0.00(1 行受影响)*/

Answer 7

--> 测试数据: #tb
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id varchar(4),charge1 numeric(4,1),charge2 numeric(4,1),charge3 numeric(4,1),charge4 numeric(4,1),charge5 numeric(4,1),charge6 numeric(4,1),charge7 numeric(4,1),charge8 numeric(4,1),charge9 numeric(4,1),charge10 numeric(4,1))
insert into #tb
select 'F101',250.5,10,50,68,85,92,60,800,200,100
declare @c1 decimal(8,2)
declare @c2 decimal(8,2)
declare @c3 decimal(8,2)
declare @c4 decimal(8,2)
declare @c5 decimal(8,2)
declare @c6 decimal(8,2)
declare @c7 decimal(8,2)
declare @c8 decimal(8,2)
declare @c9 decimal(8,2)
declare @c10 decimal(8,2)select @c1=charge1,
@c2=charge2,
@c3=charge3,
@c4=charge4,
@c5=charge5,
@c6=charge6,
@c7=charge7,
@c8=charge8,
@c9=charge9,
@c10=charge10 
from #tb
where id='F101'declare @s int=1000 if @s>@c1 set @s=@s-@c1
else select @c1=@s,@s=0  if @s>@c2 set @s=@s-@c2
else select @c2=@s,@s=0  if @s>@c3 set @s=@s-@c3
else select @c3=@s,@s=0  if @s>@c4 set @s=@s-@c4
else select @c4=@s,@s=0  if @s>@c5 set @s=@s-@c5
else select @c5=@s,@s=0  if @s>@c6 set @s=@s-@c6
else select @c6=@s,@s=0  if @s>@c7 set @s=@s-@c7
else select @c7=@s,@s=0  if @s>@c8 set @s=@s-@c8
else select @c8=@s,@s=0  if @s>@c9 set @s=@s-@c9
else select @c9=@s,@s=0  if @s>@c10 set @s=@s-@c10
else select @c10=@s,@s=0  

select @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10--------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
250.50                                  10.00                                   50.00                                   68.00                                   85.00                                   92.00                                   60.00                                   384.00                                  0.00                                    0.00(1 row(s) affected)写个。呵呵