行号 金额1 金额2 结果
1 1800 1200 1
2 500 1200 2
3 1700 1200 3
4 500 1200 4
5 600 1200 4
6 700 1200 5
1、若金额1>=金额2 则直接得出一个按顺序排列的结果 例 1800>1200 结果从1开始，所以为1
2、金额1<金额2 将将本行的金额1与下一行的金额1相加，与金额2比较 若还小于金额2 则在加上下一行的
金额1 在做比较，以此类推 ，最终这些行都得到相同的结果。 若大于金额2，则得出两个结果。
如行4，5，6 因为500+600<1200 所以在加上700 去比较，发现结果>1200 所以得出的结果为4，和5
各位大侠，用SQL怎么写出这种结果
------解决方案--------------------
with a(a,b,c)as(
select 1,1800,1200 union
select 2,500,1200 union
select 3,1700,1200 union
select 4,500,1200 union
select 5,600,1200 union
select 6,700,1200
)
,b as(
select a,b,c,case when b>c then 0 else b end d,
case when b>c then 1 else 0 end e from a where a=1
union all
select a.a,a.b,a.c,case when b.e=1 and d=0 then case when a.b>a.c
then 0 else a.b end else case when a.b+b.d>b.c then 0 else a.b+b.d end end,
case when b.d=0 then 1 when case when b.e=1 then a.b else a.b+b.d end
>b.c then 1 else 0 end
from a,b where a.a-1=b.a
),c as (
select a,b,c,MAX(e)d from b
group by a,b,c
)
select c1.a,c1.b,c1.c,SUM(c2.d) from c c1,c c2 where c1.a>=c2.a
group by c1.a,c1.b,c1.c
order by a,4
这样好像可以...试试吧，不行就等大神来解决了
------解决方案--------------------
修正為以下：
with a0(a,b,c)AS
(
select 1,1800,1200 union
select 2,500,1200 union
select 3,1700,1200 union
select 4,500,1200 union
select 5,600,1200 union
select 6,700,1200 union
select 7,200, 1200 union
select 8,200,1200 union
select 9,200,1200
)
,a AS
(
SELECT ROW_NUMBER() OVER (ORDER BY a) a,b,c
FROM a0
)
,b AS
(
select a,b,c,case when b>c then 0 else b end d,case when b>c then 1 else 0 end e
from a
where a=1
union all
select a.a,a.b,a.c,
case when a.b+b.d>b.c then CASE WHEN b.e=0 THEN a.b ELSE 0 END else a.b+b.d END,
case
when b.d=0 then 1
when case when b.e=1 then a.b else a.b+b.d end >b.c then 1
else 0
END
from a,b
WHERE a.a-1=b.a
)
,c as
(
select a,b,c,MAX(e)d from b
group by a,b,c
)
select c1.a,c1.b,c1.c,SUM(c2.d) from c c1,c c2 where c1.a>=c2.a
group by c1.a,c1.b,c1.c
order by a,4
------解决方案--------------------
-- 建表
create table ly
(行号 int,金额1 int,金额2 int,结果 int)
-- 建存储过程
create proc sp_ly
as
begin
set nocount on
declare @i int,@s int,@n int,@j1 int,@j2 int
declare ap scroll cursor for
select 行号,金额1,金额2 from ly
open ap
fetch first from ap into @n,@j1,@j2
while(@@fetch_status<>-1)
begin
if (@j1>=@j2)
begin
select @i=isnull(@i,0)+1,@s=0
end
else
begin
select @s=isnull(@s,0)+@j1
select @i=case when (@s<=@j2 and @s=@j1) or (@s>@j2) then isnull(@i,0)+1
else @i end
select @s=case when @s>@j2 then @j1 else @s end
end
update ly set 结果=@i where 行号=@n
fetch next from ap into @n,@j1,@j2
end
close ap
deallocate ap
end
-- 测试1
truncate table ly
insert into ly(行号,金额1,金额2)
select 1,1800,1200 union all
select 2,500,1200 union all
select 3,1700,1200 union all
select 4,500,1200 union all
select 5,600,1200 union all
select 6,700,1200