34,838
社区成员




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
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
然后定时作业,但是使用作业时要注意调度频度,最好放在每个月月初统计更新。--参考
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
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
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--属性--启动类型--选择"自动启动"--确定.
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
好大的虾,学习了...