求一个SQL语句

[newmcz]

有记录集
id jine
1 10
2 30
3 40
4 70
5 90
求一个SQL语句,当jine >50 后jine的直为0,即jine 累计不能>50
id jine
1 10
2 30
3 10
4 0
5 0

[dafu71]

select id, case when jine>50 then 0 else jine end jine from yourtable

[wzh1215]

update yourtable
set jine=0
where jine>50

[yujohny]

楼上的两位把问题看简单了吧，是累计>50而不是金额>50

[klan]

啥意思啊？

累计怎么解释？

先把题目说明白了

[hangaround]

MARK

[newmcz]

jine是在动态累计,当那条记录超过50就要减去超过的部分,以后的都为0,

比如,上面的10+30+40>50所以第三条就为50-10-30=10
第四条以后的都为0

[yujohny]

CREATE TABLE aa(ID INT IDENTITY(1,1),jine INT)

INSERT INTO aa VALUES(10)
INSERT INTO aa VALUES(30)
INSERT INTO aa VALUES(40)
INSERT INTO aa VALUES(70)
INSERT INTO aa VALUES(90)


--建立函数
CREATE FUNCTION IFFifty(@ID INT)
RETURNS INT
AS
BEGIN
DECLARE @zonge INT,@Result INT
SET @zonge =0
SELECT @zonge=SUM(jine) FROM aa WHERE ID <=@ID
IF @zonge<50
SELECT @Result = jine FROM aa WHERE ID =@ID
ELSE
SELECT @Result=50-SUM(jine) FROM aa WHERE ID <@ID

IF @Result <0 SET @Result =0
RETURN @Result
END

--运行更新语句
update aa
set jine=dbo.IFFifty(ID)

--看更新结果
select * from aa

--释放表
drop table aa

[yujohny]

--楼主把下面的表名aa改为你的实际的表名就可以了
--建立函数
CREATE FUNCTION IFFifty(@ID INT)
RETURNS INT
AS
BEGIN
DECLARE @zonge INT,@Result INT
SET @zonge =0
SELECT @zonge=SUM(jine) FROM aa WHERE ID <=@ID
IF @zonge<50
SELECT @Result = jine FROM aa WHERE ID =@ID
ELSE
SELECT @Result=50-SUM(jine) FROM aa WHERE ID <@ID

IF @Result <0 SET @Result =0
RETURN @Result
END

--运行更新语句
update aa
set jine=dbo.IFFifty(ID)

--看更新结果
select * from aa

--释放表
drop table aa

[yujohny]

还有记得把
--释放表
drop table aa

去掉

[newmcz]

还有没有更好的方法

[geodetic]

create trigger on tablexx
for insert|update
as
if(select sum(distinct jine) from tablexx)>50
（省略，同意 yujohny(踏网无痕)）

[FeelingL]

select id,
case when (select sum(jin) from lingling b where b.id<=a.id) >50 then 0
else (select sum(jin) from lingling b where b.id<=a.id)
end as 'jine'
from lingling a

[FeelingL]

在加一个order by id

[CA7180A2E]

declare @aa table ( id int, jine int)
insert into @aa values(1, 10)
insert into @aa values(2, 30)
insert into @aa values(3, 40)
insert into @aa values(4, 70)
insert into @aa values(5, 90)

select t2.id, case when t2.id < (select max(t1.id)
from @aa t1
where (select sum(t3.jine)
from @aa t3
where t3.id < t1.id) < 50)
then t2.jine
else 0
end
from @aa t2

[newmcz]

CA7180A2E(不求甚解) 的结果是
id
----------- -----------
1 10
2 30
3 0
4 0
5 0
这个答案还是有问题,第三条记录应是3 10
即
id
----------- -----------
1 10
2 30
3 10
4 0
5 0

[newmcz]

我自己写了一个,看看还有没有更好的方法.

declare @aa table ( id int, jine int) --//建一个测试表
declare @i as int --//声明一个变量,用来累计jine,判断是否>50
declare @id as int --//返回一个ID
declare @diff as int --//返回距50还差多少
insert into @aa values(1, 10)
insert into @aa values(2, 30)
insert into @aa values(3, 40)
insert into @aa values(4, 70)
insert into @aa values(5, 90)
set @i=0 --//初始化
set @id=0 --//初始化
while (@i<50)
begin
set @id=@id+1 --//ID号自增
select @i=@i+jine from @aa where id =@id--//让语句单条执行
end
--print @id
select @diff=50-sum(jine) from @aa where id <@id--//找出跨50的那条记录之前的累计值和50的差值
--print @diff
update @aa set jine=@diff where id=@id --//更新跨50的那条记录
update @aa set jine=0 where id>@id --//更新跨50以后的记录
select * from @aa --//返回结果

id jine
----------- -----------
1 10
2 30
3 10
4 0
5 0

[newmcz]

up

[CA7180A2E]

那就再加一個CASE好啦(逐條處理就會變得很慢).

declare @aa table ( id int, jine int)
insert into @aa values(1, 10)
insert into @aa values(2, 30)
insert into @aa values(3, 40)
insert into @aa values(4, 70)
insert into @aa values(5, 90)
insert into @aa values(6, 10)

select t2.id, case when t2.id < (select max(t1.id)
from @aa t1
where (select sum(t3.jine)
from @aa t3
where t3.id < t1.id) < 50)
then t2.jine
when t2.id = (select max(t1.id)
from @aa t1
where (select sum(t3.jine)
from @aa t3
where t3.id < t1.id) < 50)
then 50 - (select sum(t3.jine)
from @aa t3
where t3.id < t2.id)
else 0
end
from @aa t2

[newmcz]

不好意 没给： CA7180A2E(不求甚解)分,就结贴了

下次我补上