删除复制装备的SQL脚本命令

admin · 发表于 2014-4-12 20:04:09

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有帐号？立即注册

x

说明:
SQL脚本如下所示，请复制到SQL查询分析器中执行即可，建议做完备份再做些类的操作！如果你用的是GAME1数据库，请查找所有的GAME，改成GAME1，就可以了！

  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 ,
  [Place] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
  ) ON [PRIMARY]
  GO

  --首先删除玩家刚摘下来的装备，就是TBL_ITEM表中，FLD_INDEX=0的那些装备
  DELETE FROM [game].[dbo].[TBL_ITEM] WHERE [FLD_INDEX] = 0

  --从玩家身上/包袱获取数据
  INSERT INTO [game].[dbo].[TBL_ALLITEM]([FLD_CHARACTER], [FLD_TYPE], [FLD_MAKEINDEX],[FLD_TID],[FLD_INDEX],[Place])
  SELECT [FLD_CHARACTER], [FLD_TYPE], [FLD_MAKEINDEX], LTRIM(STR([FLD_MAKEINDEX]))+LTRIM(STR([FLD_INDEX])) AS FLD_TID,[FLD_INDEX],'身上' AS Place
  FROM [game].[dbo].[TBL_ITEM]

  GO

  --从仓库获取数据
  INSERT INTO [game].[dbo].[TBL_ALLITEM]([FLD_CHARACTER], [FLD_TYPE], [FLD_MAKEINDEX],[FLD_TID],[FLD_INDEX],[Place])
  SELECT [FLD_CHARACTER], [FLD_TYPE], [FLD_MAKEINDEX], LTRIM(STR([FLD_MAKEINDEX]))+LTRIM(STR([FLD_INDEX])) AS FLD_TID,[FLD_INDEX],'仓库' AS Place
  FROM [game].[dbo].[TBL_SAVEDITEM]
  GO

  --从寄售获取数据
  INSERT INTO [game].[dbo].[TBL_ALLITEM]([FLD_CHARACTER], [FLD_TYPE], [FLD_MAKEINDEX],[FLD_TID],[FLD_INDEX],[Place])
  SELECT [FLD_SELLWHO], [FLD_ITEMTYPE], [FLD_ITEM_MAKEINDEX], LTRIM(STR([FLD_ITEM_MAKEINDEX]))+LTRIM(STR([FLD_ITEM_INDEX])) AS FLD_TID,[FLD_ITEM_INDEX],'寄售' AS Place
  FROM [game].[dbo].[TBL_ITEMMARKET]
  GO

  --记录将要删除的复制装备
  if not exists (select * from dbo.sysobjects where id = object_id(N'[game].[dbo].[TBL__DELLOG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  CREATE TABLE [game].[dbo].[TBL__DELLOG] (
  [删除日期] datetime not null,
  [FLD_MAKEINDEX] [int] NOT NULL ,
  [角色名] [char] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,
  [装备代码] [int] NULL ,
  [存放地点] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
  ) ON [PRIMARY]
  GO

  INSERT INTO [game].[dbo].[TBL__DELLOG]([删除日期],[FLD_MAKEINDEX],[装备代码],[角色名],[存放地点])--,[装备名称]
  SELECT getdate() as 删除日期,[FLD_MAKEINDEX] , [FLD_INDEX]-1 AS 装备代码,[FLD_CHARACTER] AS 角色名, [Place] AS 存放地点--,[Name] AS 装备名称
  FROM [game].[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 [game].[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
  IF @V_Place = '仓库'
  DELETE FROM [game].[dbo].[TBL_SAVEDITEM] WHERE [FLD_MAKEINDEX] = @I_MAKEINDEX
  ELSE IF @V_Place = '身上'
  DELETE FROM [game].[dbo].[TBL_ITEM] WHERE [FLD_MAKEINDEX] = @I_MAKEINDEX
  ELSE
  DELETE FROM [game].[dbo].[TBL_ITEMMARKET] WHERE [FLD_ITEM_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