积分与等级问题
编号 积分 等级
1 100 1
2 200 3
3 150 2
4 300 4
如果客户达到积分要求 就得到相应的等级
比如客户积分为120 则应该等级为2
SQL2000 如何写 谢谢帮忙
编号 积分 等级
1 100 1
2 200 3
3 150 2
4 300 4
如果客户达到积分要求 就得到相应的等级
比如客户积分为120 则应该等级为2
SQL2000 如何写 谢谢帮忙
调试欢乐多
create trigger my_trig on tb for insert ,update
as
begin
declare @积分 as int
select @积分 = 积分 from tb where 编号 = (select 编号 from inserted)
update tb
set 等级 = case when @积分 between 100 and 149 then 1
when @积分 between 150 and 199 then 2
when @积分 between 200 and 299 then 3
when @积分 >= 300 then 4
end
from tb m, inserted n
where m.编号 = n.编号
end
go
ADD COLX AS
CASE WHEN [积分] BETWEEN 1-100 THEN 1
WHEN [积分] BETWEEN 100-200 THEN 2
.....
END
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date : 2009-12-03 11:09:51
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
declare @tb table (编号 int,积分 int,等级 int)
insert into @tb
select 1,100,1 union all
select 2,200,3 union all
select 3,150,2 union all
select 4,300,4declare @s intset @s=210select 等级 from (
select *,
jf=(select 积分 from @tb where 等级=b.等级+1)
from @tb b
)t
where @s between 积分 and isnull(jf,@s+1)
等级
-----------
3(1 行受影响)
,CASE WHEN [积分] BETWEEN 0 AND 120 THEN 1
WHEN [积分] BETWEEN 121 AND 180 THEN 2
...
END