chencjh 发表于 2009-3-31 13:07:40

刷贡献修正...排名前100...本人已测试...

运行

EXEC USER_KNIGHTS_RATING_UPDATE
EXEC USER_KNIGHTS_RATING_UPDATE1
EXEC UPDATE_PERSONAL_RANK


CREATE PROCEDUREUPDATE_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'.') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE .

IF EXISTS (select * from sysobjects where id = object_id(N'.') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE .

CREATE TABLE . (
    IDENTITY (1, 1) NOT NULL ,
    (21) NULL,
    NULL ,
) ON

CREATE TABLE . (
    IDENTITY (1, 1) NOT NULL ,
    (21) NULL,
    NULL ,
) ON

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

CREATEINDEX ON .() ON
CREATEINDEX ON .() ON

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'.') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE .

IF EXISTS (select * from sysobjects where id = object_id(N'.') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE .

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'.') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE .

IF EXISTS (select * from sysobjects where id = object_id(N'.') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE .

CREATE TABLE . (
    IDENTITY (1, 1) NOT NULL ,
    (21) NULL,
    NULL ,
) ON

CREATE TABLE . (
    IDENTITY (1, 1) NOT NULL ,
    (21) NULL,
    NULL ,
) ON

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

CREATEINDEX ON .() ON
CREATEINDEX ON .() ON

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'.') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE .

IF EXISTS (select * from sysobjects where id = object_id(N'.') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE .

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'.') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE .

IF EXISTS (select * from sysobjects where id = object_id(N'.') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE .

CREATE TABLE . (
    IDENTITY (1, 1) NOT NULL ,
    (21) NULL,
    (21) NULL ,
    NULL ,
) ON

CREATE TABLE . (
    IDENTITY (1, 1) NOT NULL ,
    (21) NULL,
    (21) NULL ,
    NULL ,
) ON

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

CREATEINDEX ON .() ON
CREATEINDEX ON .() ON

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'.') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE .

IF EXISTS (select * from sysobjects where id = object_id(N'.') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE .

SET NOCOUNT OFF
GO





先新建二个表..把原有的删除...


/****** Object:Table .    Script Date: 27.11.2008 19:29:52 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table .
GO

/****** Object:Table .    Script Date: 27.11.2008 19:29:53 ******/
CREATE TABLE . (
        NOT NULL ,
        (21) NOT NULL ,
        (21) NULL ,
        (21) NULL ,
        NULL ,
        (21) NULL ,
        (21) NULL ,
        NULL ,
        NOT NULL ,
        NOT NULL
) ON
GO



/****** Object:Table .    Script Date: 27.11.2008 19:30:41 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table .
GO

/****** Object:Table .    Script Date: 27.11.2008 19:30:41 ******/
CREATE TABLE . (
        NOT NULL ,
        (21) NOT NULL ,
        NOT NULL ,
        (21) NULL ,
        NULL ,
        NOT NULL ,
        NOT NULL ,
        (21) NULL ,
        NULL ,
        NOT NULL ,
        NOT NULL ,
        NOT NULL
) ON
GO

oyeono 发表于 2009-3-31 22:04:19

谢谢分享。。好多哦。。辛苦啦！

28085279 发表于 2009-4-1 12:52:21

嗯 ，非常感谢，终于搞明白了
页: [1]
查看完整版本: 刷贡献修正...排名前100...本人已测试...