WeGs
05-01-2013, 09:23 PM
السلام عليكم ورحمة الله وبركاته
الموضوع عن عمل UniqueRanking فى الداتا بيز لمعرفة من قتل اليونكات زى ما طلب العضو,,,,dodolove2888
نبدا<<<
اولا هنتخش زى ما انت بتعمل new Qurry كدا ,, database>>SRO_VT_SHARD<<<<new Qurry
وى تاخد الكود دة Copy And Past
USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_UniqueRanking_UPDATE] by karemsame// Script Date: 03/06/2012 12:44:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[_UniqueRanking_UPDATE]
@Path varchar(max), @Days int
as
SET NOCOUNT ON
Declare
@End varchar(max), @Date varchar(max),
@Start varchar(max), @1 varchar(max), @2 varchar(max), @3 varchar(max), @SEnd varchar(max);
SET @Start = (SELECT CONVERT(varchar(8), (GETDATE()-(1+(@Days))), 112))
SET @1 = SUBSTRING(@Start,1,4)
SET @2 = SUBSTRING(@Start,5,2)
SET @3 = SUBSTRING(@Start,7,2)
SET @SEnd = @1 + '-' + @2 + '-' + @3
SET @Date = @SEnd
SET @End = 'BULK INSERT dbo.#temp_fatallog FROM'+' '+'"'+@Path+'\'+@Date+'_FatalLog.txt"'
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_finalkilllog')
BEGIN DROP TABLE #temp_finalkilllog END
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_fatallog')
BEGIN DROP TABLE #temp_fatallog END
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_killlog')
BEGIN DROP TABLE #temp_killlog END
IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_FatalLog_Files')
BEGIN
CREATE TABLE _FatalLog_Files
(
[Already added] varchar(max) NULL
)
END
/*##*/
IF not exists (SELECT [Already added] FROM _FatalLog_Files WHERE [Already added] = @Date)
BEGIN
/*#*/
CREATE TABLE #temp_fatallog (
[Date1] varchar(max),
[Date2] varchar(max),
[Text1] varchar(max)
)
EXEC (@End) /*BULK INSERT*/
DELETE FROM #temp_fatallog WHERE (Text1 not like '%Unique%' OR Text1 not like '%killed%')
SELECT Date1+' '+Date2 as 'Datum', Text1 as 'Textstring' INTO #temp_killlog FROM #temp_fatallog WITH (NOLOCK) ORDER BY Date2 asc
DROP TABLE #temp_fatallog
IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_temp_UniqueRanking')
BEGIN
SELECT
SUBSTRING(Textstring,PATINDEX('%MOB%',Textstring), PATINDEX('%by%',Textstring)-(PATINDEX('%MOB%',Textstring)+2)) as 'Unique',
SUBSTRING(Textstring,PATINDEX('%by%',Textstring)+4 ,LEN(Textstring)-(PATINDEX('%by%',Textstring)+4)) as Killer
INTO _temp_UniqueRanking
FROM #temp_killlog WITH (NOLOCK)
END
ELSE BEGIN
INSERT INTO _temp_UniqueRanking
SELECT
SUBSTRING(Textstring,PATINDEX('%MOB%',Textstring), PATINDEX('%by%',Textstring)-(PATINDEX('%MOB%',Textstring)+2)) as 'Unique',
SUBSTRING(Textstring,PATINDEX('%by%',Textstring)+4 ,LEN(Textstring)-(PATINDEX('%by%',Textstring)+4)) as Killer
FROM #temp_killlog WITH (NOLOCK)
END
DROP TABLE #temp_killlog
IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_UniqueRanking')
BEGIN
SELECT temp.[Killer],
temp.[Unique],
COUNT(temp.[Unique]) as Kills
INTO _UniqueRanking
FROM _temp_UniqueRanking as temp
GROUP BY (temp.[Killer]), temp.[Unique]
ORDER BY temp.[Killer] asc
INSERT INTO _FatalLog_Files VALUES(@Date)
END
ELSE BEGIN
TRUNCATE TABLE _UniqueRanking
INSERT INTO _UniqueRanking
SELECT temp.[Killer],
temp.[Unique],
COUNT(temp.[Unique]) as Kills
FROM _temp_UniqueRanking as temp
GROUP BY (temp.[Killer]), temp.[Unique]
ORDER BY temp.[Killer] asc
INSERT INTO _FatalLog_Files VALUES(@Date)
END
/*#*/
END
/*##*/
SET NOCOUNT OFF
بعد كدا هتعمل الكويرى دى بردو
Declare @Days int, @Path varchar(max);
SET @Days = 35
SET @Path = 'D:\Blitzkrieq_Bin_Data\FatalLog'
WHILE (@Days >= 0)
BEGIN
exec _UniqueRanking_UPDATE @Path,@Days
SET @Days = @Days - 1
END
غير D:\Blitzkrieq_Bin_Data\FatalLog لى المسار بتاع ملفات السيرفر عندك
كدا تمام
هتفتح قاعدة البيانات الى اسمها SRO_VT_SHARDLOG
وى تفتح ال Table الى اسمها _UniqueRanking
هتلاقى الناس الى مموته اليونكات
لو عاوز تعرف مثلا اكتر ناس مموته يونكات معينة من هنا
SELECT
Killer,
CASE [Unique]
WHEN 'MOB_KK_ISYUTARU' THEN 'Isyutaru'
WHEN 'MOB_CH_TIGERWOMAN' THEN 'Tiger Girl'
ELSE [Unique] END as 'Unique',
Kills
FROM _UniqueRanking
WHERE [Unique] like 'MOB_KK_ISYUTARU' OR [Unique] like 'MOB_CH_TIGERWOMAN'
ORDER BY Kills desc
دى كدا هتعرفك التايجر جيلر وى الايزى عاوز تغيرها هتغير الكود بتاع اليونك وى اسمة
كدا كولو تماموز أى استفسار انا فى الخدمة قريبآ هعملها على Site:111 (4):
الموضوع عن عمل UniqueRanking فى الداتا بيز لمعرفة من قتل اليونكات زى ما طلب العضو,,,,dodolove2888
نبدا<<<
اولا هنتخش زى ما انت بتعمل new Qurry كدا ,, database>>SRO_VT_SHARD<<<<new Qurry
وى تاخد الكود دة Copy And Past
USE [SRO_VT_SHARD]
GO
/****** Object: StoredProcedure [dbo].[_UniqueRanking_UPDATE] by karemsame// Script Date: 03/06/2012 12:44:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[_UniqueRanking_UPDATE]
@Path varchar(max), @Days int
as
SET NOCOUNT ON
Declare
@End varchar(max), @Date varchar(max),
@Start varchar(max), @1 varchar(max), @2 varchar(max), @3 varchar(max), @SEnd varchar(max);
SET @Start = (SELECT CONVERT(varchar(8), (GETDATE()-(1+(@Days))), 112))
SET @1 = SUBSTRING(@Start,1,4)
SET @2 = SUBSTRING(@Start,5,2)
SET @3 = SUBSTRING(@Start,7,2)
SET @SEnd = @1 + '-' + @2 + '-' + @3
SET @Date = @SEnd
SET @End = 'BULK INSERT dbo.#temp_fatallog FROM'+' '+'"'+@Path+'\'+@Date+'_FatalLog.txt"'
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_finalkilllog')
BEGIN DROP TABLE #temp_finalkilllog END
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_fatallog')
BEGIN DROP TABLE #temp_fatallog END
IF exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '#temp_killlog')
BEGIN DROP TABLE #temp_killlog END
IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_FatalLog_Files')
BEGIN
CREATE TABLE _FatalLog_Files
(
[Already added] varchar(max) NULL
)
END
/*##*/
IF not exists (SELECT [Already added] FROM _FatalLog_Files WHERE [Already added] = @Date)
BEGIN
/*#*/
CREATE TABLE #temp_fatallog (
[Date1] varchar(max),
[Date2] varchar(max),
[Text1] varchar(max)
)
EXEC (@End) /*BULK INSERT*/
DELETE FROM #temp_fatallog WHERE (Text1 not like '%Unique%' OR Text1 not like '%killed%')
SELECT Date1+' '+Date2 as 'Datum', Text1 as 'Textstring' INTO #temp_killlog FROM #temp_fatallog WITH (NOLOCK) ORDER BY Date2 asc
DROP TABLE #temp_fatallog
IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_temp_UniqueRanking')
BEGIN
SELECT
SUBSTRING(Textstring,PATINDEX('%MOB%',Textstring), PATINDEX('%by%',Textstring)-(PATINDEX('%MOB%',Textstring)+2)) as 'Unique',
SUBSTRING(Textstring,PATINDEX('%by%',Textstring)+4 ,LEN(Textstring)-(PATINDEX('%by%',Textstring)+4)) as Killer
INTO _temp_UniqueRanking
FROM #temp_killlog WITH (NOLOCK)
END
ELSE BEGIN
INSERT INTO _temp_UniqueRanking
SELECT
SUBSTRING(Textstring,PATINDEX('%MOB%',Textstring), PATINDEX('%by%',Textstring)-(PATINDEX('%MOB%',Textstring)+2)) as 'Unique',
SUBSTRING(Textstring,PATINDEX('%by%',Textstring)+4 ,LEN(Textstring)-(PATINDEX('%by%',Textstring)+4)) as Killer
FROM #temp_killlog WITH (NOLOCK)
END
DROP TABLE #temp_killlog
IF not exists (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like '_UniqueRanking')
BEGIN
SELECT temp.[Killer],
temp.[Unique],
COUNT(temp.[Unique]) as Kills
INTO _UniqueRanking
FROM _temp_UniqueRanking as temp
GROUP BY (temp.[Killer]), temp.[Unique]
ORDER BY temp.[Killer] asc
INSERT INTO _FatalLog_Files VALUES(@Date)
END
ELSE BEGIN
TRUNCATE TABLE _UniqueRanking
INSERT INTO _UniqueRanking
SELECT temp.[Killer],
temp.[Unique],
COUNT(temp.[Unique]) as Kills
FROM _temp_UniqueRanking as temp
GROUP BY (temp.[Killer]), temp.[Unique]
ORDER BY temp.[Killer] asc
INSERT INTO _FatalLog_Files VALUES(@Date)
END
/*#*/
END
/*##*/
SET NOCOUNT OFF
بعد كدا هتعمل الكويرى دى بردو
Declare @Days int, @Path varchar(max);
SET @Days = 35
SET @Path = 'D:\Blitzkrieq_Bin_Data\FatalLog'
WHILE (@Days >= 0)
BEGIN
exec _UniqueRanking_UPDATE @Path,@Days
SET @Days = @Days - 1
END
غير D:\Blitzkrieq_Bin_Data\FatalLog لى المسار بتاع ملفات السيرفر عندك
كدا تمام
هتفتح قاعدة البيانات الى اسمها SRO_VT_SHARDLOG
وى تفتح ال Table الى اسمها _UniqueRanking
هتلاقى الناس الى مموته اليونكات
لو عاوز تعرف مثلا اكتر ناس مموته يونكات معينة من هنا
SELECT
Killer,
CASE [Unique]
WHEN 'MOB_KK_ISYUTARU' THEN 'Isyutaru'
WHEN 'MOB_CH_TIGERWOMAN' THEN 'Tiger Girl'
ELSE [Unique] END as 'Unique',
Kills
FROM _UniqueRanking
WHERE [Unique] like 'MOB_KK_ISYUTARU' OR [Unique] like 'MOB_CH_TIGERWOMAN'
ORDER BY Kills desc
دى كدا هتعرفك التايجر جيلر وى الايزى عاوز تغيرها هتغير الكود بتاع اليونك وى اسمة
كدا كولو تماموز أى استفسار انا فى الخدمة قريبآ هعملها على Site:111 (4):