Ranking DBF

Chaosduckman

Push

tester1337

Nehmt doch gleich die Offi RankingDB.

Code

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 
) ON [PRIMARY]
GO


CREATE     PROC 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_szGuild,m_szName,
	m_nWin,m_nLose,m_nSurrender,m_MaximumUnity,m_AvgLevel,
	m_nGuildGold,m_nWinPoint,m_nPlayTime,CreateTime
	FROM RANKING_TBL
	WHERE Gu = ''' + @iGu + '''
	AND serverindex = ''' + @iserverindex + '''
	AND s_date =  ''' + @os_date + '''' + @orderby
)
RETURN


CREATE proc usp_guildbank_log_view
@iGu char(2) = 'S1',
@im_idGuild char(6) = '01',
@iserverindex CHAR(2) = ''
AS
SET NOCOUNT ON


declare @q1 nvarchar(4000)
declare @q2 nvarchar(4000)


IF @iGu = 'S1'
BEGIN 
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex AND State=''A''  ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END 
/*
넣기 A 빼기 D 돈넣기 I 돈빼기 O
*/
ELSE
IF @iGu = 'S2'
BEGIN
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex  AND State=''D''   ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END
/*
        
*/
ELSE
IF @iGu = 'S3'
BEGIN
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex  AND State=''I''   ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END
/*
        
*/
ELSE
IF @iGu = 'S4'
BEGIN
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex AND State=''O''    ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END
/*
        
*/
RETURN

Alles anzeigen

Damit die RANKING_TBL auch gefüllt werden kann, könnt ihr die MAKE_RANKING_STR in die CHARACTER_01_DBF einfügen:

Code

CREATE         PROC MAKE_RANKING_STR
@iserverindex CHAR(2) = '01'
AS
set nocount on
DECLARE @currDate char(10),@om_nCount INT,@of_nCount INT				
SET @currDate = CONVERT(CHAR(8),GETDATE(),112) 
						   + RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(hh,GETDATE())),2) 
				--		   + RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(mi,GETDATE())),2)




--R1 : 최강길드
--R2 : 최다승
--R3 : 최다패
--R4 : 최다항복패
--R5 : 최고결속력
--R6 : 최고자금
--R7 : 평균고랩
--R8 : 최대플레이


--SELECT * FROM RANKING.RANKING_DBF.dbo.RANKING_TBL
--DELETE RANKING.RANKING_DBF.dbo.RANKING_TBL
--R1 : 최강길드


IF EXISTS(SELECT * FROM RANKING.RANKING_DBF.dbo.RANKING_TBL WHERE s_date = @currDate and serverindex = @iserverindex)
BEGIN
	DELETE RANKING.RANKING_DBF.dbo.RANKING_TBL WHERE s_date = @currDate and serverindex = @iserverindex
END


BEGIN
INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R1',s_date = @currDate,-- m_Title = '최강길드',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),				
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
		AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority ='F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_nWinPoint DESC,m_nWin DESC






--R2 : 최다승


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R2',s_date = @currDate,--m_Title = '최다승',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),				
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
       AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_nWin DESC,CreateTime


--R3 : 최다패


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R3',s_date = @currDate,-- m_Title = '최다패',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),				
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
       AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_nLose DESC,m_nSurrender DESC


--R4 : 최다항복패


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R4',s_date = @currDate,-- m_Title = '최다항복패',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),				
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
       AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_nSurrender DESC,m_nLose DESC


--R5 : 최고결속력


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R5',s_date = @currDate,-- m_Title = '최고결속력',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),				
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
       AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_MaximumUnity DESC,CreateTime


--R6 : 최고자금


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
select top 20 *
from (
SELECT TOP 20 Gu = 'R6', s_date = @currDate,-- m_Title = '최고자금',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(dbo.fn_GuildGold(A.m_nGuildGold)),0),
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
      	AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
	and A.m_nGuildGold > 0
  GROUP BY B.m_idGuild
ORDER BY m_nGuildGold DESC,CreateTime
/*union all
SELECT TOP 20 Gu = 'R6', s_date = @currDate,-- m_Title = '최고자금',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
--				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold + 4294967295),0),
				m_nGuildGold = isnull(max(dbo.fn_GuildGold(A.m_nGuildGold)), 0),
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
      	AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
	and A.m_nGuildGold < 0
  GROUP BY B.m_idGuild
ORDER BY A.m_nGuildGold DESC,CreateTime*/)x
order by m_nGuildGold desc, CreateTime
/*SELECT TOP 20 Gu = 'R6',s_date = @currDate,-- m_Title = '최고자금',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),				
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
      	AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_nGuildGold DESC,CreateTime
*/


--R7 : 평균고랩


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R7',s_date = @currDate,-- m_Title = '평균고랩',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
       AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_AvgLevel DESC,CreateTime


--R8 : 최대플레이


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R8',s_date = @currDate,-- m_Title = '평균고랩',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
      	AND C.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_nPlayTime DESC,CreateTime
END

Alles anzeigen

Chaosduckman

Danke ich schaue es mir mal an^^

Chaosduckman

Push it! Leider hatte ich noch keine Zeit eine "Final" DB zu machen, da ich gerade mit meinem C++ Entpacker beschäftigt bin und ich diesen noch fertig machen will. Sobald dieser fertig ist kommt die DB!

Chaosduckman

Push it up Leider immernoch Entpacker xD

Valron

Zitat von TwiLight;108369

Nehmt doch gleich die Offi RankingDB.

Code

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 
) ON [PRIMARY]
GO


CREATE     PROC 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_szGuild,m_szName,
	m_nWin,m_nLose,m_nSurrender,m_MaximumUnity,m_AvgLevel,
	m_nGuildGold,m_nWinPoint,m_nPlayTime,CreateTime
	FROM RANKING_TBL
	WHERE Gu = ''' + @iGu + '''
	AND serverindex = ''' + @iserverindex + '''
	AND s_date =  ''' + @os_date + '''' + @orderby
)
RETURN


CREATE proc usp_guildbank_log_view
@iGu char(2) = 'S1',
@im_idGuild char(6) = '01',
@iserverindex CHAR(2) = ''
AS
SET NOCOUNT ON


declare @q1 nvarchar(4000)
declare @q2 nvarchar(4000)


IF @iGu = 'S1'
BEGIN 
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex AND State=''A''  ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END 
/*
넣기 A 빼기 D 돈넣기 I 돈빼기 O
*/
ELSE
IF @iGu = 'S2'
BEGIN
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex  AND State=''D''   ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END
/*
        
*/
ELSE
IF @iGu = 'S3'
BEGIN
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex  AND State=''I''   ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END
/*
        
*/
ELSE
IF @iGu = 'S4'
BEGIN
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex AND State=''O''    ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END
/*
        
*/
RETURN

Alles anzeigen

Damit die RANKING_TBL auch gefüllt werden kann, könnt ihr die MAKE_RANKING_STR in die CHARACTER_01_DBF einfügen:

Code

CREATE         PROC MAKE_RANKING_STR
@iserverindex CHAR(2) = '01'
AS
set nocount on
DECLARE @currDate char(10),@om_nCount INT,@of_nCount INT				
SET @currDate = CONVERT(CHAR(8),GETDATE(),112) 
						   + RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(hh,GETDATE())),2) 
				--		   + RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(mi,GETDATE())),2)




--R1 : 최강길드
--R2 : 최다승
--R3 : 최다패
--R4 : 최다항복패
--R5 : 최고결속력
--R6 : 최고자금
--R7 : 평균고랩
--R8 : 최대플레이


--SELECT * FROM RANKING.RANKING_DBF.dbo.RANKING_TBL
--DELETE RANKING.RANKING_DBF.dbo.RANKING_TBL
--R1 : 최강길드


IF EXISTS(SELECT * FROM RANKING.RANKING_DBF.dbo.RANKING_TBL WHERE s_date = @currDate and serverindex = @iserverindex)
BEGIN
	DELETE RANKING.RANKING_DBF.dbo.RANKING_TBL WHERE s_date = @currDate and serverindex = @iserverindex
END


BEGIN
INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R1',s_date = @currDate,-- m_Title = '최강길드',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),				
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
		AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority ='F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_nWinPoint DESC,m_nWin DESC






--R2 : 최다승


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R2',s_date = @currDate,--m_Title = '최다승',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),				
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
       AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_nWin DESC,CreateTime


--R3 : 최다패


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R3',s_date = @currDate,-- m_Title = '최다패',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),				
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
       AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_nLose DESC,m_nSurrender DESC


--R4 : 최다항복패


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R4',s_date = @currDate,-- m_Title = '최다항복패',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),				
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
       AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_nSurrender DESC,m_nLose DESC


--R5 : 최고결속력


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R5',s_date = @currDate,-- m_Title = '최고결속력',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),				
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
       AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_MaximumUnity DESC,CreateTime


--R6 : 최고자금


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
select top 20 *
from (
SELECT TOP 20 Gu = 'R6', s_date = @currDate,-- m_Title = '최고자금',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(dbo.fn_GuildGold(A.m_nGuildGold)),0),
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
      	AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
	and A.m_nGuildGold > 0
  GROUP BY B.m_idGuild
ORDER BY m_nGuildGold DESC,CreateTime
/*union all
SELECT TOP 20 Gu = 'R6', s_date = @currDate,-- m_Title = '최고자금',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
--				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold + 4294967295),0),
				m_nGuildGold = isnull(max(dbo.fn_GuildGold(A.m_nGuildGold)), 0),
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
      	AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
	and A.m_nGuildGold < 0
  GROUP BY B.m_idGuild
ORDER BY A.m_nGuildGold DESC,CreateTime*/)x
order by m_nGuildGold desc, CreateTime
/*SELECT TOP 20 Gu = 'R6',s_date = @currDate,-- m_Title = '최고자금',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),				
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
      	AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_nGuildGold DESC,CreateTime
*/


--R7 : 평균고랩


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R7',s_date = @currDate,-- m_Title = '평균고랩',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
       AND A.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_AvgLevel DESC,CreateTime


--R8 : 최대플레이


INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
		(
				Gu,	s_date,
				serverindex,
				m_dwLogo,
				m_idGuild,
				m_szGuild,
				m_szName,
				m_nWin,
				m_nLose,
				m_nSurrender,
				m_MaximumUnity,
				m_AvgLevel,
				m_nGuildGold,
				m_nWinPoint,
				m_nPlayTime,
				CreateTime
		)
SELECT TOP 20 Gu = 'R8',s_date = @currDate,-- m_Title = '평균고랩',
				serverindex = MAX(A.serverindex),
				m_dwLogo = MAX(A.m_dwLogo),
				B.m_idGuild,
				m_szGuild = MAX(A.m_szGuild),
				m_szName = MAX(C.m_szName),
				m_nWin = MAX(A.m_nWin),
				m_nLose = MAX(A.m_nLose)+	MAX(A.m_nSurrender),
				m_nSurrender = MAX(A.m_nSurrender),
				m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
				m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
				m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
				m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
				m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
				CreateTime = MAX(A.CreateTime)
    FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
  WHERE A.m_idGuild = B.m_idGuild
      	AND C.serverindex = E.serverindex
		AND C.m_idPlayer =  E.m_idPlayer
       AND B.m_nMemberLv = 0
       AND B.m_idPlayer = C.m_idPlayer
		AND A.serverindex = B.serverindex
		AND B.serverindex = C.serverindex
		AND C.serverindex = @iserverindex
		AND D.m_chLoginAuthority = 'F'
		AND C.account = D.account
  GROUP BY B.m_idGuild
ORDER BY m_nPlayTime DESC,CreateTime
END

Alles anzeigen

Bekomme hier einen Error:

Zitat

Meldung 156, Ebene 15, Status 1, Prozedur RANKING_STR, Zeile 41
Falsche Syntax in der Nähe des 'proc'-Schlüsselwortes.
Meldung 137, Ebene 15, Status 2, Prozedur RANKING_STR, Zeile 58
Die "@im_idGuild"-Skalarvariable muss deklariert werden.
Meldung 137, Ebene 15, Status 2, Prozedur RANKING_STR, Zeile 71
Die "@im_idGuild"-Skalarvariable muss deklariert werden.
Meldung 137, Ebene 15, Status 2, Prozedur RANKING_STR, Zeile 84
Die "@im_idGuild"-Skalarvariable muss deklariert werden.
Meldung 137, Ebene 15, Status 2, Prozedur RANKING_STR, Zeile 97
Die "@im_idGuild"-Skalarvariable muss deklariert werden.

Alles anzeigen

@Threadersteller:
Wenn man es bei File-Upload lädt, ist das Archiv beschädigt und
bei Megaupload ist die Datei nicht verfügbar. ;)

MfG,
Valron

Chaosduckman

Mhhh ok dann bastel ich halt heute doch die DB und werde sie hochladen xD
Danke für den Tipp^^ Ka was mit Megaupload los ist ?!

Den Fehler habe ich auch^^
Meldung 156, Ebene 15, Status 1, Prozedur RANKING_STR, Zeile 41
Falsche Syntax in der Nähe des 'proc'-Schlüsselwortes.
Aber der Rest ERROR LOGS LESEN FTW ;)

tester1337

Zitat von Valron;110059

Bekomme hier einen Error:

@Threadersteller:
Wenn man es bei File-Upload lädt, ist das Archiv beschädigt und
bei Megaupload ist die Datei nicht verfügbar. ;)
MfG,
Valron

Dann führe die beiden Skripts doch einzeln aus? xD

Oder benutze den [COLOR="Red"]QUOTED IDENTIFIER[/COLOR].

Code

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 
) ON [PRIMARY]
GO


[COLOR="Red"]SET QUOTED_IDENTIFIER ON
GO[/COLOR] 
SET ANSI_NULLS ON 
GO


CREATE     PROC 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_szGuild,m_szName,
	m_nWin,m_nLose,m_nSurrender,m_MaximumUnity,m_AvgLevel,
	m_nGuildGold,m_nWinPoint,m_nPlayTime,CreateTime
	FROM RANKING_TBL
	WHERE Gu = ''' + @iGu + '''
	AND serverindex = ''' + @iserverindex + '''
	AND s_date =  ''' + @os_date + '''' + @orderby
)
RETURN


GO
[COLOR="Red"]SET QUOTED_IDENTIFIER OFF 
GO[/COLOR]
SET ANSI_NULLS ON 
GO


[COLOR="Red"]SET QUOTED_IDENTIFIER ON 
GO[/COLOR]
SET ANSI_NULLS ON 
GO


CREATE proc usp_guildbank_log_view
@iGu char(2) = 'S1',
@im_idGuild char(6) = '01',
@iserverindex CHAR(2) = ''
AS
SET NOCOUNT ON


declare @q1 nvarchar(4000)
declare @q2 nvarchar(4000)


IF @iGu = 'S1'
BEGIN 
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex AND State=''A''  ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END 
/*
넣기 A 빼기 D 돈넣기 I 돈빼기 O
*/
ELSE
IF @iGu = 'S2'
BEGIN
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex  AND State=''D''   ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END
/*
        
*/
ELSE
IF @iGu = 'S3'
BEGIN
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex  AND State=''I''   ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END
/*
        
*/
ELSE
IF @iGu = 'S4'
BEGIN
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex AND State=''O''    ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END
/*
        
*/
RETURN
GO
[COLOR="Red"]SET QUOTED_IDENTIFIER OFF 
GO[/COLOR]
SET ANSI_NULLS ON 
GO

Alles anzeigen

Chaosduckman

mhh doppelpost xD ich bedanke mich mal beim obersten xD

WTF?

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Tippfehler ;)

tester1337

Zitat von Chaosduckman;110081

mhh doppelpost xD ich bedanke mich mal beim obersten xD
WTF?
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Tippfehler ;)

Alles anzeigen

Wo soll da ein Tippfehler sein?

Chaosduckman

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Du machst SET QUOTED_IDENTIFIER aus und gleich wieder an?! Ich weiß nicht ob das so sein muss da ich kein MSSQL kann :/

tester1337

Zitat von Chaosduckman;110088

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Du machst SET QUOTED_IDENTIFIER aus und gleich wieder an?! Ich weiß nicht ob das so sein muss da ich kein MSSQL kann :/

Alles anzeigen

Einmal für die RANKING_STR (Da kommt das OFF), dann sofort wieder ON (wegen der usp_guildbanklogview)

Du musst die den Quoted Identifier so vorstellen:
Er trennt die einzelnen Queries, man kann es mit zitieren vergleichen (Englisch to quote = zitieren). Bei der RANKING_STR zum Beispiel steht am Ende "RETURN".
Bei SQL kommt bei RETURN normalerweise etwas danach, hier aber nicht. Sollten nach der Query für die RANKING_STR noch weitere folgen, müsste hier der Quoted Identifier genutzt werden (Hab ich erst jetzt bemerkt), damit die einzelnen Codes getrennt werden können.

Chaosduckman

Ahhh danke^^ Ok jetzt ist mir einiges klarer xD

Valron

Zitat von TwiLight;110079

Dann führe die beiden Skripts doch einzeln aus? xD

Oder benutze den [COLOR="Red"]QUOTED IDENTIFIER[/COLOR].

Code

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 
) ON [PRIMARY]
GO


[COLOR="Red"]SET QUOTED_IDENTIFIER ON
GO[/COLOR] 
SET ANSI_NULLS ON 
GO


CREATE     PROC 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_szGuild,m_szName,
	m_nWin,m_nLose,m_nSurrender,m_MaximumUnity,m_AvgLevel,
	m_nGuildGold,m_nWinPoint,m_nPlayTime,CreateTime
	FROM RANKING_TBL
	WHERE Gu = ''' + @iGu + '''
	AND serverindex = ''' + @iserverindex + '''
	AND s_date =  ''' + @os_date + '''' + @orderby
)
RETURN


GO
[COLOR="Red"]SET QUOTED_IDENTIFIER OFF 
GO[/COLOR]
SET ANSI_NULLS ON 
GO


[COLOR="Red"]SET QUOTED_IDENTIFIER ON 
GO[/COLOR]
SET ANSI_NULLS ON 
GO


CREATE proc usp_guildbank_log_view
@iGu char(2) = 'S1',
@im_idGuild char(6) = '01',
@iserverindex CHAR(2) = ''
AS
SET NOCOUNT ON


declare @q1 nvarchar(4000)
declare @q2 nvarchar(4000)


IF @iGu = 'S1'
BEGIN 
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex AND State=''A''  ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END 
/*
넣기 A 빼기 D 돈넣기 I 돈빼기 O
*/
ELSE
IF @iGu = 'S2'
BEGIN
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex  AND State=''D''   ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END
/*
        
*/
ELSE
IF @iGu = 'S3'
BEGIN
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex  AND State=''I''   ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END
/*
        
*/
ELSE
IF @iGu = 'S4'
BEGIN
set @q1 = '
SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
WHERE m_idGuild = @im_idGuild  AND serverindex = @iserverindex AND State=''O''    ORDER BY s_date DESC'
set @q2 = replace(@q1, '[&server&]', @iserverindex)
exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
END
/*
        
*/
RETURN
GO
[COLOR="Red"]SET QUOTED_IDENTIFIER OFF 
GO[/COLOR]
SET ANSI_NULLS ON 
GO

Alles anzeigen

Ok, danke. :)
Hat geklappt. ^.^

Chaosduckman

-Push-

Chaosduckman

-Push-
Soo ich werbe immernoch fürs Selber machen xD

Chaosduckman

-Push-
Vote for selfmade =)

Ranking DBF

Jetzt mitmachen!

SCHNELLZUGRIFF

HILFE & COMMUNITY

RECHTLICHES