还有一种,采用作业,每天零时执行一次。create proc get_grb as begin declare @每日贵人榜上榜费用 select @每日贵人榜上榜费用 = 1 insert 贵人榜 select 姓名,审核时间 from 人员列表 where 费用>=@每日贵人榜上榜费用 update 人员列表 set 费用 = 费用 - @每日贵人榜上榜费用 where 费用>=@每日贵人榜上榜费用 select * from 贵人榜 end
表结构 CREATE TABLE [dbo].[Signiors]( [SigniorID] [int] IDENTITY(1,1) NOT NULL, [UserID] [int] NOT NULL, [ApplyGRBSReferT] [datetime] NULL,/*提交“贵人榜显示”申请 的时间*/ [SigniorState][int] null,/*审核状态,已审核为1,未审核但已经提交申请为0,*/ [GRBAuditPassT] [datetime] NULL,/*审核通过时间*/ [ApplyGRBSTF] [int] NULL,/*想要在“贵人榜”显示的天数*/ [BeginTime] [datetime] NULL,/*在贵人榜显示的开始时间*/ [EndTime] [datetime] NULL/*在贵人榜显示的结束时间*/ CONSTRAINT [PK__Signiors__1273C1CD] PRIMARY KEY CLUSTERED ( [SigniorID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]存储过程:(只实现了排队显示,请大家帮助实现哈 续费就回到榜上) ALTER proc [dbo].[spCFAGRB] @UserID int, @GRBAuditPassT datetime, @NowTime datetime as declare @WUserID int, @BeginTime datetime, @UID int, @ApplyGRBSTF int update Signiors set GRBAuditPassT=@GRBAuditPassT, SigniorState=1 where UserID=@UserIDselect @WUserID=count(*) from Signiors where BeginTime<@NowTime and EndTime>@NowTime select @ApplyGRBSTF=ApplyGRBSTF from Signiors where UserID=@UserID SELECT TOP (1) @BeginTime=EndTime,@UID=UserID FROM Signiors ORDER BY EndTime asc if (@WUserID<=12) update Signiors set BeginTime=@NowTime, EndTime=@NowTime+@ApplyGRBSTF where UserID=@UserID if(@WUserID>12) update Signiors set BeginTime=@BeginTime, EndTime=@BeginTime+@ApplyGRBSTF where UserID=@UID
as
begin
declare @每日贵人榜上榜费用
select @每日贵人榜上榜费用 = 1
insert 贵人榜 select 姓名,审核时间 from 人员列表 where 费用>=@每日贵人榜上榜费用
update 人员列表 set 费用 = 费用 - @每日贵人榜上榜费用 where 费用>=@每日贵人榜上榜费用
select * from 贵人榜
end
CREATE TABLE [dbo].[Signiors](
[SigniorID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[ApplyGRBSReferT] [datetime] NULL,/*提交“贵人榜显示”申请 的时间*/
[SigniorState][int] null,/*审核状态,已审核为1,未审核但已经提交申请为0,*/
[GRBAuditPassT] [datetime] NULL,/*审核通过时间*/
[ApplyGRBSTF] [int] NULL,/*想要在“贵人榜”显示的天数*/
[BeginTime] [datetime] NULL,/*在贵人榜显示的开始时间*/
[EndTime] [datetime] NULL/*在贵人榜显示的结束时间*/
CONSTRAINT [PK__Signiors__1273C1CD] PRIMARY KEY CLUSTERED
(
[SigniorID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]存储过程:(只实现了排队显示,请大家帮助实现哈 续费就回到榜上)
ALTER proc [dbo].[spCFAGRB]
@UserID int,
@GRBAuditPassT datetime,
@NowTime datetime
as
declare @WUserID int,
@BeginTime datetime,
@UID int,
@ApplyGRBSTF int
update Signiors
set GRBAuditPassT=@GRBAuditPassT,
SigniorState=1
where UserID=@UserIDselect @WUserID=count(*) from Signiors where BeginTime<@NowTime and EndTime>@NowTime
select @ApplyGRBSTF=ApplyGRBSTF from Signiors where UserID=@UserID
SELECT TOP (1) @BeginTime=EndTime,@UID=UserID FROM Signiors ORDER BY EndTime asc
if (@WUserID<=12)
update Signiors
set BeginTime=@NowTime,
EndTime=@NowTime+@ApplyGRBSTF
where UserID=@UserID
if(@WUserID>12)
update Signiors
set BeginTime=@BeginTime,
EndTime=@BeginTime+@ApplyGRBSTF
where UserID=@UID