根据消费情况自动调整会员级别的问题

[sy_binbin]

有2个表，一个是会员表，一个是订单表

会员表
id int
level int --会员级别 1普通会员 2银牌会员 3金牌会员 4VIP会员


订单表
id int
userID int
price money
times datetime


要求是，会员最近三个月消费金额累计打到2500的，会员级别升到银牌会员
会员最近三个月消费金额累计打到5000的，会员级别升到金牌会员
会员最近三个月消费金额累计打到8000的，会员级别升到VIP会员

客户那面打算要自动完成这个功能，所以我打算用作业去做。
研究了半天，SQL语句不知道怎么去写了！呵呵！！会的写个大概思路就可以！

[gcw633]

好大的虾，学习了...

[nianran520]

update r
set level = case when level=1 and t.price >= 2500 and t.price < 5000 then 2
when level in (1,2) and t.price >= 5000 and t.price < 8000 then 3
when level in (1,2,3) and t.price >= 8000 then 4
from 会员表 r
(select userID,sum(price) as price
from 订单表
where datediff(month,times,getdate())<=3
group by userID) t
where r.id =t.userID
 

[dawugui]

先创建一个计算的存储过程.

create proc my_proc
as
update 会员表
set level = (case when n.price >= 8000 then 4
                  when n.price >= 5000 and n.price < 8000 then 3
                  when n.price >= 2500 and n.price < 5000 then 2
                  else level
             end)
from 会员表 m ,
(select userID , sum(price) price from 订单表 where datediff(mm,times,getdate()) < 3 or (datediff(mm,times,getdate())=3 and right(convert(varchar(10),times,120),5) >= right(convert(varchar(10),getdate(),120),5) ) group by userID) n
where m.id = n.userID
go

定时作业的制定

企业管理器 
--管理 
--SQL Server代理 
--右键作业 
--新建作业 
--"常规"项中输入作业名称 
--"步骤"项 
--新建 
--"步骤名"中输入步骤名 
--"类型"中选择"Transact-SQL 脚本(TSQL)" 
--"数据库"选择执行命令的数据库 
--"命令"中输入要执行的语句: 
                       EXEC my_proc 

--确定 
--"调度"项 
--新建调度 
--"名称"中输入调度名称 
--"调度类型"中选择你的作业执行安排 
--如果选择"反复出现" 
--点"更改"来设置你的时间安排  


然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行 

设置方法: 
我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定. 

[smntbk]

是不是需要考虑这样一个问题：级别回滚
就是这次统计结果出来后，该会员消费金额达到5000了，是金牌级别，
下个月统计时，消费金额只达到了2500.
那么不允许把级别改为银牌了吧

[sgtzzc]

update a
set a.level=case when b.price>=2500 and b.price<5000 then 2
                 when b.price>=5000 and b.price<8000 then 3
                 when b.price>=8000  then 4
                 else 1
            end
from
  会员表 a,
  (select userID,sum(price) as price from 订单表 
    where datediff(mm,times,getdate())<=3
    group by userID) b
where 
  a.userid=b.userid

[--小F--]

--参考
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER trigger [tri_updatesalary_Mem_Type]
 on [dbo].[SalaryIncrease]
after  insert
as
 declare @i int
 set @i=@@identity
 
 update m
 set m.Mem_Type=case when s.SMark>500000 then '退休会员'--500000
                   when s.SMark>400000 then '钻石五级'--400000
                   when s.SMark>300000 then '钻石四级'--300000
                   when s.SMark>200000 then '钻石三级'--200000
                   when s.SMark>100000 then '钻石二级'--100000
                   when s.SMark>50000 then '钻石一级'--50000
                   when s.SMark>40000 then '五星级'--40000
                   when s.SMark>30000 then '四星级'--30000
                   when s.SMark>20000 then '三星级'--20000
                   when s.SMark>10000 then '二星级'--10000
                   when s.SMark>5000 then '一星级'--5000
                   else '一般VIP会员'
               end
from MemberInfo m
  join (select s.SCardName,sum(s.SMark) as SMark 
          from SalaryIncrease s join inserted i on s.SCardName=i.SCardName group by s.SCardName) s
    on m.Mem_Num=s.SCardName 
 
 --if exists(select * from MemberInfo m join inserted i on m.Mem_Num=i.SCardName and m.Mem_Mark>100 )
 if exists (select  m.SCardName,sum(m.ShopMark)as mark from(select m.SCardName,m.ShopMark  from ShoppingMark m join inserted i on m.SCardName=i.SCardName
where year(m.SDate)=year(getdate()) and month(m.SDate)=month(getdate())) m
 group by m.SCardName  having sum(m.ShopMark)>100)
 /**/
/* --最新的(我写的,上面是整合魅影的)select * from ShoppingMark SM join (select m.SCardName,sum(m.ShopMark) as Mark from ShoppingMark m join inserted i on m.SCardName=i.SCardName group by m.SCardName) s
 on SM.SCardName=s.ScardName where month(SDate)=month(getdate()) and year(SDate)=year(getdate()) and s.Mark>100*/
begin
 update s
   set s.SIncease=case
                      --积分大于1000就是星级.所以不用判断是否是星级
                   when m.Mem_Type<>'一般VIP会员' then s.SMark*0.3
                   else case 
                          when s.SMark>4000 then 
                               s.SMark*0.3
                        --  when s.SMark>4000 then 
                             --  200*0.2+200*0.23+200*0.25+200*0.28+(s.SMark-800)*0.3
                          when s.SMark>3000 then 
                               1000*0.2+1000*0.23+1000*0.25+(s.SMark-600)*0.28
                          when s.SMark>2000 then 
                               1000*0.2+100*0.23+(s.SMark-400)*0.25  
                          when s.SMark>1000 then
                               (s.SMark-200)*.023+1000*0.2
                          else  s.SMark*0.2
                       end
                  end
 from SalaryIncrease as s
 join inserted i
    on s.SCardName=i.SCardName
  join MemberInfo m
    on (i.SCardName=m.Mem_Num and s.SID=@i) or (i.SCardName=m.Mem_Num and s.SIncease=0)
    end
--go

[sy_binbin]

[Quote=引用 3 楼 smntbk 的回复:]
是不是需要考虑这样一个问题：级别回滚
就是这次统计结果出来后，该会员消费金额达到5000了，是金牌级别，
下个月统计时，消费金额只达到了2500.
那么不允许把级别改为银牌了吧
[/Quote]

嗯！考虑的很周到！呵呵
这个问题我再和客户那面去研究下！

[jack_ailly]

update a
set a.level=(case when n.price >= 8000 then 4
                  when n.price >= 5000 and n.price < 8000 then 3
                  when n.price >= 2500 and n.price < 5000 then 2
                  else level
             end)
from 会员表 a,
  (select userID,sum(price) as price from 订单表 
    where datediff(mm,times,getdate())<=3
    group by userID) b
where 
  a.userid=b.userid

然后定时作业，但是使用作业时要注意调度频度，最好放在每个月月初统计更新。

[dawugui]

[Quote=引用 6 楼 sy_binbin 的回复:]
引用 3 楼 smntbk 的回复:
是不是需要考虑这样一个问题：级别回滚
就是这次统计结果出来后，该会员消费金额达到5000了，是金牌级别，
下个月统计时，消费金额只达到了2500.
那么不允许把级别改为银牌了吧


嗯！考虑的很周到！呵呵
这个问题我再和客户那面去研究下！
[/Quote]

create proc my_proc
as
update 会员表
set level = (case when n.price >= 8000 then 4
                  when level < 3 and n.price >= 5000 and n.price < 8000 then 3
                  when level < 2 and n.price >= 2500 and n.price < 5000 then 2
                  else level
             end)
from 会员表 m ,
(select userID , sum(price) price from 订单表 where datediff(mm,times,getdate()) < 3 or (datediff(mm,times,getdate())=3 and right(convert(varchar(10),times,120),5) >= right(convert(varchar(10),getdate(),120),5) ) group by userID) n
where m.id = n.userID
go

[nianran520]

[Quote=引用 6 楼 sy_binbin 的回复:]
引用 3 楼 smntbk 的回复:
是不是需要考虑这样一个问题：级别回滚
就是这次统计结果出来后，该会员消费金额达到5000了，是金牌级别，
下个月统计时，消费金额只达到了2500.
那么不允许把级别改为银牌了吧


嗯！考虑的很周到！呵呵
这个问题我再和客户那面去研究下！
[/Quote]1楼已经考虑了这问题

[sy_binbin]

嗯！知道了！呵呵！！

[7761098]

1楼正解了，都很速度啊，现在

[qiqi860819]

case when 判断，更新等级

[happinessdepig]

学习。。