运行
EXEC USER_KNIGHTS_RATING_UPDATE
EXEC USER_KNIGHTS_RATING_UPDATE1
EXEC UPDATE_PERSONAL_RANK
CREATE PROCEDURE UPDATE_PERSONAL_RANK
AS
/*
Author : AKUMA
E-Mail : [email protected]
Edit By ESTHECAT
E-Mail : [email protected]
*/
SET NOCOUNT ON
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[KUSER_RATING_MONTHLY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[KUSER_RATING_MONTHLY]
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[EUSER_RATING_MONTHLY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[EUSER_RATING_MONTHLY]
CREATE TABLE [dbo].[KUSER_RATING_MONTHLY] (
[nRank] [int] IDENTITY (1, 1) NOT NULL ,
[strUserID] [char] (21) NULL,
[nLoyaltyMonthly] [int] NULL ,
) ON [PRIMARY]
CREATE TABLE [dbo].[EUSER_RATING_MONTHLY] (
[nRank] [int] IDENTITY (1, 1) NOT NULL ,
[strUserID] [char] (21) NULL,
[nLoyaltyMonthly] [int] NULL ,
) ON [PRIMARY]
INSERT INTO KUSER_RATING_MONTHLY
SELECT TOP 100 USERDATA.strUserId, USERDATA.LoyaltyMonthly
FROM USERDATA
WHERE (USERDATA.Nation = 1) AND (USERDATA.City <> 255 ) AND (Authority = 1)
ORDER BY USERDATA.LoyaltyMonthly DESC
INSERT INTO EUSER_RATING_MONTHLY
SELECT TOP 100 USERDATA.strUserId, USERDATA.LoyaltyMonthly
FROM USERDATA
WHERE (USERDATA.Nation = 2) AND (USERDATA.City <> 255 ) AND (Authority = 1)
ORDER BY USERDATA.LoyaltyMonthly DESC
CREATE INDEX [IX_RATING_RANK] ON [dbo].[KUSER_RATING_MONTHLY]([nRank]) ON [PRIMARY]
CREATE INDEX [IX_RATING_RANK] ON [dbo].[EUSER_RATING_MONTHLY]([nRank]) ON [PRIMARY]
DECLARE @CheckUserPersonalRankTable tinyint
SELECT @CheckUserPersonalRankTable = Count(*) FROM USER_PERSONAL_RANK
IF @CheckUserPersonalRankTable < 100
BEGIN
DECLARE @RankName varchar(30)
DECLARE @I int
SET @I = 1
WHILE @I < 101
BEGIN
IF @I = 1
BEGIN
SET @RankName = 'Gold Knight'
END
IF @I > 1 AND @I <= 4
BEGIN
SET @RankName = 'Silver Knight'
END
IF @I > 4 AND @I <= 9
BEGIN
SET @RankName = 'Mirage Knight'
END
IF @I > 9 AND @I <= 10
BEGIN
SET @RankName = 'Shadow Knight'
END
IF @I > 25 AND @I <= 50
BEGIN
SET @RankName = 'Mist Knight'
END
IF @I > 50 AND @I <= 100
BEGIN
SET @RankName = 'Training Knight'
END
INSERT INTO USER_PERSONAL_RANK VALUES (@I,@RankName,0,NULL,0,1000000,0,NULL,NULL,1000000,1000000,GETDATE())
SET @I = @I + 1
END
END
DECLARE @strCharID char(21)
DECLARE @nIndex smallint
DECLARE @nLoyaltyMonthly int
SET @strCharID = NULL
SET @nIndex = 0
SET @nLoyaltyMonthly = 0
DECLARE CRS_KARUS CURSOR FOR
SELECT nRank, strUserID, nLoyaltyMonthly FROM KUSER_RATING_MONTHLY
OPEN CRS_KARUS
FETCH NEXT FROM CRS_KARUS
INTO @nIndex, @strCharID, @nLoyaltyMonthly
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE USER_PERSONAL_RANK SET strKarusUserID = @strCharID, nKarusLoyaltyMonthly = @nLoyaltyMonthly WHERE nRank = @nIndex
FETCH NEXT FROM CRS_KARUS
INTO @nIndex, @strCharID, @nLoyaltyMonthly
END
CLOSE CRS_KARUS
DEALLOCATE CRS_KARUS
DECLARE CRS_HUMAN CURSOR FOR
SELECT nRank, strUserID, nLoyaltyMonthly FROM EUSER_RATING_MONTHLY
OPEN CRS_HUMAN
FETCH NEXT FROM CRS_HUMAN
INTO @nIndex, @strCharID, @nLoyaltyMonthly
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE USER_PERSONAL_RANK SET strElmoUserID = @strCharID, nElmoLoyaltyMonthly = @nLoyaltyMonthly WHERE nRank = @nIndex
FETCH NEXT FROM CRS_HUMAN
INTO @nIndex, @strCharID, @nLoyaltyMonthly
END
CLOSE CRS_HUMAN
DEALLOCATE CRS_HUMAN
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[KUSER_RATING_MONTHLY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[KUSER_RATING_MONTHLY]
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[EUSER_RATING_MONTHLY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[EUSER_RATING_MONTHLY]
SET NOCOUNT OFF
GO
CREATE PROCEDURE USER_KNIGHTS_RATING_UPDATE
AS
/*
Author : AKUMA
E-Mail : [email protected]
Edit By ESTHECAT
E-Mail : [email protected]
*/
SET NOCOUNT ON
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[KUSER_RATING]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[KUSER_RATING]
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[EUSER_RATING]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[EUSER_RATING]
CREATE TABLE [dbo].[KUSER_RATING] (
[nRank] [int] IDENTITY (1, 1) NOT NULL ,
[strUserID] [char] (21) NULL,
[nLoyalty] [int] NULL ,
) ON [PRIMARY]
CREATE TABLE [dbo].[EUSER_RATING] (
[nRank] [int] IDENTITY (1, 1) NOT NULL ,
[strUserID] [char] (21) NULL,
[nLoyalty] [int] NULL ,
) ON [PRIMARY]
INSERT INTO KUSER_RATING
SELECT TOP 100 USERDATA.strUserId, USERDATA.Loyalty FROM USERDATA
WHERE (USERDATA.Nation = 1) AND (USERDATA.City <> 255 ) AND (Authority = 1)
ORDER BY USERDATA.Loyalty DESC
INSERT INTO EUSER_RATING
SELECT TOP 100 USERDATA.strUserId, USERDATA.Loyalty FROM USERDATA
WHERE (USERDATA.Nation = 2) AND (USERDATA.City <> 255 ) AND (Authority = 1)
ORDER BY USERDATA.Loyalty DESC
CREATE INDEX [IX_RATING_RANK] ON [dbo].[KUSER_RATING]([nRank]) ON [PRIMARY]
CREATE INDEX [IX_RATING_RANK] ON [dbo].[EUSER_RATING]([nRank]) ON [PRIMARY]
DECLARE @CheckUserKnightsRankTable tinyint
SELECT @CheckUserKnightsRankTable = Count(*) FROM USER_KNIGHTS_RANK
IF @CheckUserKnightsRankTable < 100
BEGIN
DECLARE @RankName varchar(30)
DECLARE @I int
SET @I = 1
WHILE @I < 101
BEGIN
IF @I = 1
BEGIN
SET @RankName = 'Gold Knight'
END
IF @I > 1 AND @I <= 4
BEGIN
SET @RankName = 'Silver Knight'
END
IF @I > 4 AND @I <= 9
BEGIN
SET @RankName = 'Mirage Knight'
END
IF @I > 9 AND @I <= 10
BEGIN
SET @RankName = 'Shadow Knight'
END
IF @I > 25 AND @I <= 50
BEGIN
SET @RankName = 'Mist Knight'
END
IF @I > 50 AND @I <= 100
BEGIN
SET @RankName = 'Training Knight'
END
INSERT INTO USER_KNIGHTS_RANK VALUES (@I,@RankName,NULL,NULL,0,NULL,NULL,0,1000000,GETDATE())
SET @I = @I + 1
END
END
DECLARE @strCharID char(21)
DECLARE @strKnightsID char(21)
DECLARE @nIndex smallint
DECLARE @nLoyalty int
SET @strCharID = NULL
SET @strKnightsID = NULL
SET @nIndex = 0
SET @nLoyalty = 0
DECLARE CRS_KARUS CURSOR FOR
SELECT nRank, strUserID, nLoyalty FROM KUSER_RATING
OPEN CRS_KARUS
FETCH NEXT FROM CRS_KARUS
INTO @nIndex, @strCharID, @nLoyalty
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE USER_KNIGHTS_RANK SET strKarusUserID = @strCharID, strKarusKnightsName = @strKnightsID, nKarusLoyalty = @nLoyalty WHERE shIndex = @nIndex
FETCH NEXT FROM CRS_KARUS
INTO @nIndex, @strCharID, @nLoyalty
END
CLOSE CRS_KARUS
DEALLOCATE CRS_KARUS
DECLARE CRS_HUMAN CURSOR FOR
SELECT nRank, strUserID, nLoyalty FROM EUSER_RATING
OPEN CRS_HUMAN
FETCH NEXT FROM CRS_HUMAN
INTO @nIndex, @strCharID, @nLoyalty
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE USER_KNIGHTS_RANK SET strElmoUserID = @strCharID, strElmoKnightsName = @strKnightsID, nElmoLoyalty = @nLoyalty WHERE shIndex = @nIndex
FETCH NEXT FROM CRS_HUMAN
INTO @nIndex, @strCharID, @nLoyalty
END
CLOSE CRS_HUMAN
DEALLOCATE CRS_HUMAN
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[KUSER_RATING]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[KUSER_RATING]
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[EUSER_RATING]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[EUSER_RATING]
SET NOCOUNT OFF
GO
CREATE PROCEDURE USER_KNIGHTS_RATING_UPDATE1
AS
/*
Author : AKUMA
E-Mail : [email protected]
Edit By ESTHECAT
E-Mail : [email protected]
*/
SET NOCOUNT ON
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[KUSER_RATING]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[KUSER_RATING]
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[EUSER_RATING]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[EUSER_RATING]
CREATE TABLE [dbo].[KUSER_RATING] (
[nRank] [int] IDENTITY (1, 1) NOT NULL ,
[strUserID] [char] (21) NULL,
[strKnightsName] [char] (21) NULL ,
[nLoyalty] [int] NULL ,
) ON [PRIMARY]
CREATE TABLE [dbo].[EUSER_RATING] (
[nRank] [int] IDENTITY (1, 1) NOT NULL ,
[strUserID] [char] (21) NULL,
[strKnightsName] [char] (21) NULL ,
[nLoyalty] [int] NULL ,
) ON [PRIMARY]
INSERT INTO KUSER_RATING
SELECT TOP 100 USERDATA.strUserId, KNIGHTS.IDName, USERDATA.Loyalty FROM USERDATA INNER JOIN KNIGHTS ON USERDATA.Knights = KNIGHTS.IDNum
WHERE (USERDATA.Nation = 1) AND (USERDATA.City <> 255 ) AND (Authority = 1)
ORDER BY USERDATA.Loyalty DESC
INSERT INTO EUSER_RATING
SELECT TOP 100 USERDATA.strUserId, KNIGHTS.IDName, USERDATA.Loyalty FROM USERDATA INNER JOIN KNIGHTS ON USERDATA.Knights = KNIGHTS.IDNum
WHERE (USERDATA.Nation = 2) AND (USERDATA.City <> 255 ) AND (Authority = 1)
ORDER BY USERDATA.Loyalty DESC
CREATE INDEX [IX_RATING_RANK] ON [dbo].[KUSER_RATING]([nRank]) ON [PRIMARY]
CREATE INDEX [IX_RATING_RANK] ON [dbo].[EUSER_RATING]([nRank]) ON [PRIMARY]
DECLARE @CheckUserKnightsRankTable tinyint
SELECT @CheckUserKnightsRankTable = Count(*) FROM USER_KNIGHTS_RANK
IF @CheckUserKnightsRankTable < 100
BEGIN
DECLARE @RankName varchar(30)
DECLARE @I int
SET @I = 1
WHILE @I < 101
BEGIN
IF @I = 1
BEGIN
SET @RankName = 'Gold Knight'
END
IF @I > 1 AND @I <= 4
BEGIN
SET @RankName = 'Silver Knight'
END
IF @I > 4 AND @I <= 9
BEGIN
SET @RankName = 'Mirage Knight'
END
IF @I > 9 AND @I <= 10
BEGIN
SET @RankName = 'Shadow Knight'
END
IF @I > 25 AND @I <= 50
BEGIN
SET @RankName = 'Mist Knight'
END
IF @I > 50 AND @I <= 100
BEGIN
SET @RankName = 'Training Knight'
END
INSERT INTO USER_KNIGHTS_RANK VALUES (@I,@RankName,NULL,NULL,0,NULL,NULL,0,1000000,GETDATE())
SET @I = @I + 1
END
END
DECLARE @strCharID char(21)
DECLARE @strKnightsID char(21)
DECLARE @nIndex smallint
DECLARE @nLoyalty int
SET @strCharID = NULL
SET @strKnightsID = NULL
SET @nIndex = 0
SET @nLoyalty = 0
DECLARE CRS_KARUS CURSOR FOR
SELECT nRank, strUserID, strKnightsName, nLoyalty FROM KUSER_RATING
OPEN CRS_KARUS
FETCH NEXT FROM CRS_KARUS
INTO @nIndex, @strCharID, @strKnightsID, @nLoyalty
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE USER_KNIGHTS_RANK SET strKarusUserID = @strCharID, strKarusKnightsName = @strKnightsID, nKarusLoyalty = @nLoyalty WHERE shIndex = @nIndex
FETCH NEXT FROM CRS_KARUS
INTO @nIndex, @strCharID, @strKnightsID, @nLoyalty
END
CLOSE CRS_KARUS
DEALLOCATE CRS_KARUS
DECLARE CRS_HUMAN CURSOR FOR
SELECT nRank, strUserID, strKnightsName, nLoyalty FROM EUSER_RATING
OPEN CRS_HUMAN
FETCH NEXT FROM CRS_HUMAN
INTO @nIndex, @strCharID, @strKnightsID, @nLoyalty
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE USER_KNIGHTS_RANK SET strElmoUserID = @strCharID, strElmoKnightsName = @strKnightsID, nElmoLoyalty = @nLoyalty WHERE shIndex = @nIndex
FETCH NEXT FROM CRS_HUMAN
INTO @nIndex, @strCharID, @strKnightsID, @nLoyalty
END
CLOSE CRS_HUMAN
DEALLOCATE CRS_HUMAN
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[KUSER_RATING]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[KUSER_RATING]
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].[EUSER_RATING]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[EUSER_RATING]
SET NOCOUNT OFF
GO
先新建二个表..把原有的删除...
/****** Object: Table [dbo].[USER_KNIGHTS_RANK] Script Date: 27.11.2008 19:29:52 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USER_KNIGHTS_RANK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[USER_KNIGHTS_RANK]
GO
/****** Object: Table [dbo].[USER_KNIGHTS_RANK] Script Date: 27.11.2008 19:29:53 ******/
CREATE TABLE [dbo].[USER_KNIGHTS_RANK] (
[shIndex] [smallint] NOT NULL ,
[strName] [char] (21) NOT NULL ,
[strElmoUserID] [varchar] (21) NULL ,
[strElmoKnightsName] [char] (21) NULL ,
[nElmoLoyalty] [int] NULL ,
[strKarusUserID] [varchar] (21) NULL ,
[strKarusKnightsName] [char] (21) NULL ,
[nKarusLoyalty] [int] NULL ,
[nMoney] [int] NOT NULL ,
[UpdateDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[USER_PERSONAL_RANK] Script Date: 27.11.2008 19:30:41 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USER_PERSONAL_RANK]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[USER_PERSONAL_RANK]
GO
/****** Object: Table [dbo].[USER_PERSONAL_RANK] Script Date: 27.11.2008 19:30:41 ******/
CREATE TABLE [dbo].[USER_PERSONAL_RANK] (
[nRank] [smallint] NOT NULL ,
[strPosition] [char] (21) NOT NULL ,
[nElmoUP] [smallint] NOT NULL ,
[strElmoUserID] [char] (21) NULL ,
[nElmoLoyaltyMonthly] [int] NULL ,
[nElmoCheck] [int] NOT NULL ,
[nKarusUP] [smallint] NOT NULL ,
[strKarusUserID] [char] (21) NULL ,
[nKarusLoyaltyMonthly] [int] NULL ,
[nKarusCheck] [int] NOT NULL ,
[nSalary] [int] NOT NULL ,
[UpdateDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO |
