传奇3.0讨论常要用到的SQL脚本

王朝厨房·作者佚名  2007-01-02
窄屏简体版  字體: |||超大  

常要用到的SQL脚本 --说明:此SQL脚本专为删除复制装备而写

--思路:将所有玩家仓库/身上/包袱中的装备全部放入一个临时表,再从临时表中查询MAKEINDEX重复的装备并遂个删除

-- 删除的记录记在日志文件TBL__DELLOG中。

--日期:2003/04/29

--修正日期:2003/05/20

--改进了算法,只列出makeindex相同且装备代码相同的物品。

--改动处:LTRIM(STR([FLD_MAKEINDEX]))+LTRIM(STR([FLD_INDEX])) AS FLD_TID

USE muddb

GO

---建立临时表

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBL_ALLITEM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[TBL_ALLITEM]

GO

CREATE TABLE [dbo].[TBL_ALLITEM] (

[FLD_CHARACTER] [char] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[FLD_TYPE] [tinyint] NULL ,

[FLD_MAKEINDEX] [int] NOT NULL ,

[FLD_TID] [varchar] (30) NOT NULL ,

[FLD_INDEX] [int] NOT NULL ,

[Name] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL,

[Place] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

GO

--从玩家身上/包袱获取数据

INSERT INTO [muddb].[dbo].[TBL_ALLITEM]([FLD_CHARACTER], [FLD_TYPE], [FLD_MAKEINDEX],[FLD_TID],[FLD_INDEX],[Name],[Place])

SELECT [FLD_CHARACTER], [FLD_TYPE], [FLD_MAKEINDEX], LTRIM(STR([FLD_MAKEINDEX]))+LTRIM(STR([FLD_INDEX])) AS FLD_TID,[FLD_INDEX],[Name],'身上' AS Place

FROM [muddb].[dbo].[TBL_ITEM],[muddb].[dbo].[StdItems]

WHERE [FLD_INDEX]-1=[Idx]

GO

--从仓库获取数据

INSERT INTO [muddb].[dbo].[TBL_ALLITEM]([FLD_CHARACTER], [FLD_TYPE], [FLD_MAKEINDEX],[FLD_TID],[FLD_INDEX],[Name],[Place])

SELECT [FLD_CHARACTER], [FLD_TYPE], [FLD_MAKEINDEX], LTRIM(STR([FLD_MAKEINDEX]))+LTRIM(STR([FLD_INDEX])) AS FLD_TID,[FLD_INDEX],[Name],'仓库' AS Place

FROM [muddb].[dbo].[TBL_SAVEDITEM],[muddb].[dbo].[StdItems]

WHERE [FLD_INDEX]-1=[Idx]

GO

--记录将要删除的复制装备

if not exists (select * from dbo.sysobjects where id = object_id(N'[muddb].[dbo].[TBL__DELLOG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

CREATE TABLE [muddb].[dbo].[TBL__DELLOG] (

[删除日期] datetime not null,

[FLD_MAKEINDEX] [int] NOT NULL ,

[角色名] [char] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[装备代码] [int] NULL ,

[装备名称] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL,

[存放地点] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

GO

INSERT INTO [muddb].[dbo].[TBL__DELLOG]([删除日期],[FLD_MAKEINDEX],[装备代码],[装备名称],[角色名],[存放地点])

SELECT getdate() as 删除日期,[FLD_MAKEINDEX] , [FLD_INDEX]-1 AS 装备代码,[Name] AS 装备名称,[FLD_CHARACTER] AS 角色名, [Place] AS 存放地点

FROM [muddb].[dbo].[TBL_ALLITEM]

WHERE ((([FLD_TID]) In

(SELECT [FLD_TID] FROM [TBL_ALLITEM] As Tmp GROUP BY [FLD_TID] HAVING Count([FLD_TID])>1 )))

ORDER BY [FLD_MAKEINDEX]

GO

--遂个删除复制装备

DECLARE @T_ID char (30),@I_MAKEINDEX int , @V_Place varchar (10)

DECLARE D_ITEMS_cursor CURSOR FOR

SELECT [FLD_TID],[FLD_MAKEINDEX],[Place]

FROM [muddb].[dbo].[TBL_ALLITEM]

WHERE ((([FLD_TID]) In

(SELECT [FLD_TID] FROM [TBL_ALLITEM] As Tmp GROUP BY [FLD_TID]

HAVING Count([FLD_TID])>1 )))

ORDER BY [FLD_MAKEINDEX]

OPEN D_ITEMS_cursor

FETCH NEXT FROM D_ITEMS_cursor INTO @T_ID,@I_MAKEINDEX,@V_Place

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN

IF @V_Place = '仓库'

DELETE FROM [muddb].[dbo].[TBL_SAVEDITEM] WHERE [FLD_MAKEINDEX] = @I_MAKEINDEX

ELSE

DELETE FROM [muddb].[dbo].[TBL_ITEM] WHERE [FLD_MAKEINDEX] = @I_MAKEINDEX

END

FETCH NEXT FROM D_ITEMS_cursor INTO @T_ID,@I_MAKEINDEX,@V_Place

END

CLOSE D_ITEMS_cursor

DEALLOCATE D_ITEMS_cursor

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBL_ALLITEM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[TBL_ALLITEM]

GO

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航