USE [RANKING_DBF]
GO
/****** Object: Table [dbo].[RANKING_TBL] Script Date: 04/03/2010 12:49:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RANKING_TBL](
[order] [int] NULL,
[order_all] [int] NULL,
[Gu] [char](2) NOT NULL,
[s_date] [char](10) NOT NULL,
[serverindex] [char](2) NOT NULL,
[m_dwLogo] [int] NULL,
[m_idGuild] [char](6) NOT NULL,
[m_szGuild] [varchar](48) NULL,
[m_szName] [varchar](32) NULL,
[m_nWin] [int] NULL,
[m_nLose] [int] NULL,
[m_nSurrender] [int] NULL,
[m_MaximumUnity] [float] NULL,
[m_AvgLevel] [float] NULL,
[m_nGuildGold] [bigint] NULL,
[m_nWinPoint] [int] NULL,
[m_nPlayTime] [int] NULL,
[CreateTime] [datetime] NULL,
CONSTRAINT [PK_RANKING_TBL] PRIMARY KEY CLUSTERED
(
[Gu] ASC,
[s_date] ASC,
[serverindex] ASC,
[m_idGuild] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: View [dbo].[view_last_3_month] Script Date: 04/03/2010 12:49:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[view_last_3_month]
AS
SELECT X.account FROM
(
SELECT A.account FROM LOG_01_DBF.dbo.CHARACTER_TBL A,
(SELECT m_idPlayer,serverindex FROM LOG_01_DBF.dbo.VIEW_LOG_LOGIN_TBL
WHERE End_Time >= CONVERT(CHAR(8),DATEADD(m,-3,GETDATE()) ,112)
GROUP BY m_idPlayer,serverindex
HAVING MIN(End_Time) >= CONVERT(CHAR(8),DATEADD(m,-3,GETDATE()) ,112)) B
WHERE A.m_idPlayer = B.m_idPlayer
AND A.serverindex = B.serverindex
GROUP BY A.account
) X
GO
/****** Object: View [dbo].[view_last_1_month] Script Date: 04/03/2010 12:49:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[view_last_1_month]
AS
SELECT X.account FROM
(
SELECT A.account FROM LOG_01_DBF.dbo.CHARACTER_TBL A,
(SELECT m_idPlayer,serverindex FROM LOG_01_DBF.dbo.VIEW_LOG_LOGIN_TBL
WHERE End_Time >= CONVERT(CHAR(8),DATEADD(m,-1,GETDATE()) ,112)
GROUP BY m_idPlayer,serverindex
HAVING MIN(End_Time) >= CONVERT(CHAR(8),DATEADD(m,-1,GETDATE()) ,112)) B
WHERE A.m_idPlayer = B.m_idPlayer
AND A.serverindex = B.serverindex
GROUP BY A.account
) X
GO
/****** Object: StoredProcedure [dbo].[usp_guildbank_log_view] Script Date: 04/03/2010 12:48:58 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC [dbo].[usp_guildbank_log_view]
@iGu char(2) = 'S1',
@im_idGuild char(6) = '01',
@iserverindex CHAR(2) = ''
AS
SET NOCOUNT ON
IF @iGu = 'S1'
BEGIN
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_cou nt
FROM LOGGING_01_DBF.dbo.LOG_GUILD_BANK_TBL
WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State='A' ORDER BY s_date DESC
END
/*
넣기 A 빼기 D 돈넣기 I 돈빼기 O
*/
ELSE
IF @iGu = 'S2'
BEGIN
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_cou nt
FROM LOGGING_01_DBF.dbo.LOG_GUILD_BANK_TBL
WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State='D' ORDER BY s_date DESC
END
/*
*/
ELSE
IF @iGu = 'S3'
BEGIN
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_cou nt
FROM LOGGING_01_DBF.dbo.LOG_GUILD_BANK_TBL
WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State='I' ORDER BY s_date DESC
END
/*
*/
ELSE
IF @iGu = 'S4'
BEGIN
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_cou nt
FROM LOGGING_01_DBF.dbo.LOG_GUILD_BANK_TBL
WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State='O' ORDER BY s_date DESC
END
/*
*/
RETURN
SET NOCOUNT OFF
GO
/****** Object: StoredProcedure [dbo].[RANKING_STR] Script Date: 04/03/2010 12:48:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[RANKING_STR]
@iGu CHAR(2) = 'R1',
@iserverindex CHAR(2) = '01'
AS
DECLARE @os_date CHAR(10)
SELECT @os_date = MAX(s_date) FROM RANKING_TBL WHERE Gu = @iGu AND serverindex = @iserverindex
DECLARE @orderby VARCHAR(255)
--R1 : 최강길드
--R2 : 최다승
--R3 : 최다패
--R4 : 최다항복패
--R5 : 최고결속력
--R6 : 최고자금
--R7 : 평균고랩
--R8 : 최대플레이
SELECT @orderby = CASE @iGu WHEN 'R1' THEN ' ORDER BY m_nWinPoint DESC,m_nWin DESC'
WHEN 'R2' THEN ' ORDER BY m_nWin DESC,CreateTime'
WHEN 'R3' THEN ' ORDER BY m_nLose DESC,m_nSurrender DESC'
WHEN 'R4' THEN ' ORDER BY m_nSurrender DESC,m_nLose DESC'
WHEN 'R5' THEN ' ORDER BY m_MaximumUnity DESC,CreateTime'
WHEN 'R6' THEN ' ORDER BY m_nGuildGold DESC,CreateTime'
WHEN 'R7' THEN ' ORDER BY m_AvgLevel DESC,CreateTime'
WHEN 'R8' THEN ' ORDER BY m_nPlayTime DESC,CreateTime' END
EXEC
(
'SELECT TOP 20 [order],Gu,s_date,serverindex,m_dwLogo,m_idGuild,m_szGuil d,m_szName,
m_nWin,m_nLose,m_nSurrender,m_MaximumUnity,m_AvgLe vel,
m_nGuildGold,m_nWinPoint,m_nPlayTime,CreateTime
FROM RANKING_TBL
WHERE Gu = ''' + @iGu + '''
AND serverindex = ''' + @iserverindex + '''
AND s_date = ''' + @os_date + '''' + @orderby
)
RETURN
GO
|
Nein damit verschwinden die Gilden auch nicht mehr