2005-03-10, 03:14 AM | #1 |
注册日期: 2003-10-22
帖子: 11,051
积分:6
精华:24
现金:14342金币
资产:29325299金币
|
配套SQL查询指令
--作者:口吃的男人 --说明:此SQL脚本专为删除复制装备而写 --思路:将所有玩家仓库/身上/包袱中的装备全部放入一个临时表,再从临时表中查询MAKEINDEX重复的装备并遂个删除 -- 删除的记录记在日志文件TBL__DELLOG中。 --日期:2003/04/29 --修正日期:2003/05/20 ---建立临时表 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 -------------------------------------------------------------- --作者:口吃的男人 --说明:此SQL脚本专为删除垃圾数据而写 --思路:提取所有已删除或者级别小于21的角色名最后一次登陆在2003年7月30号以前的, 并删除与此角色相关的装备及技能 --最后修正日期:2003/05/20 WHERE [FLD_DELETED]=1 AND FLD_UPDATEDATETIME < '2003 - 7 - 30' (删除7月30以前没有登陆的所有用户) ------------------------------------------------------------------ DECLARE @username char(15) DECLARE D_lj_cursor CURSOR FOR SELECT [FLD_CHARACTER] FROM [muddb].[dbo].[TBL_CHARACTER] WHERE [FLD_DELETED]=1 OR ([FLD_LEVEL]<21 AND FLD_UPDATEDATETIME < '2003 - 7 - 30') OPEN D_lj_cursor FETCH NEXT FROM D_lj_cursor INTO @username WHILE @@FETCH_STATUS = 0 BEGIN PRINT '正在删除 '+@username+' 的相关资料......' DELETE FROM [muddb].[dbo].[TBL_MAGIC] WHERE [FLD_CHARACTER] = @username DELETE FROM [muddb].[dbo].[TBL_ITEM] WHERE [FLD_CHARACTER] = @username DELETE FROM [muddb].[dbo].[TBL_SAVEDITEM] WHERE [FLD_CHARACTER] = @username DELETE FROM [muddb].[dbo].[TBL_QUEST] WHERE [FLD_CHARACTER] = @username DELETE FROM [muddb].[dbo].[TBL_SKILL] WHERE [FLD_CHARACTER] = @username DELETE FROM [muddb].[dbo].[TBL_CURRENTABILITY] WHERE [FLD_CHARACTER] = @username DELETE FROM [muddb].[dbo].[TBL_ABILITY] WHERE [FLD_CHARACTER] = @username DELETE FROM [muddb].[dbo].[TBL_CHARACTER] WHERE [FLD_CHARACTER] = @username FETCH NEXT FROM D_lj_cursor INTO @username END CLOSE D_lj_cursor DEALLOCATE D_lj_cursor ------------------------------------------------------------------ SQL中查询自己定意级别,多久没登录的语句! SQL中执行上面命令就可以了 SELECT * FROM TBL_CHARACTER WHERE (FLD_LEVEL < 21) AND (FLD_UPDATEDATETIME < '2003 - 7 - 30') -------------------------------------------------------------- 查询人物 SELECT * FROM TBL_CHARACTER WHERE (FLD_INDEX = '34') -------------------------------------------------------------- --人身上的物品代码为34的物品全部删除掉,还有仓库的 DELETE FROM [muddb].[dbo].[TBL_ITEM] WHERE [FLD_INDEX]=34 DELETE FROM [muddb].[dbo].[TBL_SAVEDITEM] WHERE [FLD_INDEX]=34 ------------------------------------------------------------- /*查找身上与箱子里的复制物品*/ SQL中执行上面命令就可以了 SELECT * FROM TBL_ITEM WHERE (FLD_MAKEINDEX IN (SELECT fld_makeindex FROM tbl_saveditem)) ORDER BY FLD_MAKEINDEX -------------------------------------------------------------- --角色改名,所有相关资料随同转移到新角色上面。 --@username 原名 --@username1 新角色名 DECLARE @username varchar(10),@username1 varchar(10) set @username='口吃的男人' set @username1='不再口吃' UPDATE [muddb].[dbo].[TBL_QUEST] SET [FLD_CHARACTER] = @username1 WHERE [FLD_CHARACTER] like @username UPDATE [muddb].[dbo].[TBL_MAGIC] SET [FLD_CHARACTER] = @username1 WHERE [FLD_CHARACTER] like @username UPDATE [muddb].[dbo].[TBL_ITEM] SET [FLD_CHARACTER] = @username1 WHERE [FLD_CHARACTER] like @username UPDATE [muddb].[dbo].[TBL_SAVEDITEM] SET [FLD_CHARACTER] = @username1 WHERE [FLD_CHARACTER] like @username UPDATE [muddb].[dbo].[TBL_CHARACTER] SET [FLD_CHARACTER] = @username1 WHERE [FLD_CHARACTER] like @username UPDATE [muddb].[dbo].[TBL_ABILITY] SET [FLD_CHARACTER] = @username1 WHERE [FLD_CHARACTER] like @username ------------------------------------------------------------------ --调级 DECLARE @username varchar(10) set @username='口吃的男人' UPDATE [muddb].[dbo].[TBL_CHARACTER] SET [FLD_LEVEL]=40 WHERE [FLD_CHARACTER] like @username UPDATE [muddb].[dbo].[TBL_ABILITY] SET [FLD_LEVEL]=40 WHERE [FLD_CHARACTER] like @username ------------------------------------------------------------------ --删除技能 DELETE FROM [muddb].[dbo].[TBL_MAGIC] --删除包中的物品 DELETE FROM [muddb].[dbo].[TBL_ITEM] --删除仓库物品 DELETE FROM [muddb].[dbo].[TBL_SAVEDITEM] --删除任务数据 DELETE FROM [muddb].[dbo].[TBL_QUEST] --调整所有玩家级别为七级,金钱为5000,经验为0 UPDATE [muddb].[dbo].[TBL_CHARACTER] SET [FLD_LEVEL]=7, [FLD_GOLD]=5000 UPDATE [muddb].[dbo].[TBL_ABILITY] SET [FLD_LEVEL]=7,[FLD_EXP] = 0 ---------------------------------------------------------------- SELECT * FROM TBL_CHARACTER where FLD_CHARACTER='非凡' 选择所有项目来自于表格TBL_CHARACTER,列出符合条件为"非凡"的人物名称. SELECT * FROM TBL_MAGIC where FLD_CHARACTER='xtwfgypm' SELECT * FROM TBL_CHARACTER where FLD_USERID='3680550' 选择所有项目来自于表格TBL_CHARACTER,列出符合条件为"3680550"的帐号id SELECT * FROM TBL_SAVEDITEM where FLD_DURA='41000' 选择所有项目来自于表格TBL_SAVEDITEM(人物的仓库),列出符合条件为"41000"的持久度或纯度. SELECT * FROM TBL_ITEM where FLD_INDEX='286' 选择所有项目来自于表格TBL_ITEM(人物包袱),列出符合条件为"286"的物品序号 SELECT * FROM TBL_ITEM where FLD_MAKEINDEX='220076414' 选择所有项目来自于表格TBL_ITEM(人物包袱),列出符合条件为"220076414"的制造时间戳. |
|