传奇3.0--常要用到的SQL脚本

来源：风闪网络学院） 作者： 出处：巧巧读书 2007-10-28 进入讨论组

• 关 键 词：
• 日志文件

常要用到的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

• 用Photoshop给漂亮的烫发MM抠图 
• Photoshop透明婚纱抠图大法 
• Photoshop:让MM做个“变色龙” 
• 用Photoshop来制作一款精美的宝宝照片墙 
• Photoshop绝色美女通道抠图法 
• 用Photoshop教你打造绚丽光芒效果 

   巧巧读书:http://www.qqread.com/sqlserver/2007/10/y362974.html

更多内容请看脚本攻击和防范专题，或进入讨论组讨论。

【收藏此文】【大 中 小】【打印】【关闭】

较早的文章：传奇3.0--使用SQL数据浏览器来导入和修改你的数据库

较新的文章：减少SQL日志的方法