Ranking DBF

    • Werbung zur Unterstützung des Forums ( Bitte AddBlocker deaktivieren )

    • Nehmt doch gleich die Offi RankingDB.

      Quellcode

      1. CREATE TABLE [dbo].[RANKING_TBL] (
      2. [order] [int] NULL ,
      3. [order_all] [int] NULL ,
      4. [Gu] [char] (2) NOT NULL ,
      5. [s_date] [char] (10) NOT NULL ,
      6. [serverindex] [char] (2) NOT NULL ,
      7. [m_dwLogo] [int] NULL ,
      8. [m_idGuild] [char] (6) NOT NULL ,
      9. [m_szGuild] [varchar] (48) NULL ,
      10. [m_szName] [varchar] (32) NULL ,
      11. [m_nWin] [int] NULL ,
      12. [m_nLose] [int] NULL ,
      13. [m_nSurrender] [int] NULL ,
      14. [m_MaximumUnity] [float] NULL ,
      15. [m_AvgLevel] [float] NULL ,
      16. [m_nGuildGold] [bigint] NULL ,
      17. [m_nWinPoint] [int] NULL ,
      18. [m_nPlayTime] [int] NULL ,
      19. [CreateTime] [datetime] NULL
      20. ) ON [PRIMARY]
      21. GO
      23. CREATE PROC RANKING_STR
      24. @iGu CHAR(2) = 'R1',
      25. @iserverindex CHAR(2) = '01'
      26. AS
      27. DECLARE @os_date CHAR(10)
      28. SELECT @os_date = MAX(s_date) FROM RANKING_TBL WHERE Gu = @iGu AND serverindex = @iserverindex
      30. DECLARE @orderby VARCHAR(255)
      32. --R1 : 최강길드
      33. --R2 : 최다승
      34. --R3 : 최다패
      35. --R4 : 최다항복패
      36. --R5 : 최고결속력
      37. --R6 : 최고자금
      38. --R7 : 평균고랩
      39. --R8 : 최대플레이
      41. SELECT @orderby = CASE @iGu WHEN 'R1' THEN ' ORDER BY m_nWinPoint DESC,m_nWin DESC'
      42. WHEN 'R2' THEN ' ORDER BY m_nWin DESC,CreateTime'
      43. WHEN 'R3' THEN ' ORDER BY m_nLose DESC,m_nSurrender DESC'
      44. WHEN 'R4' THEN ' ORDER BY m_nSurrender DESC,m_nLose DESC'
      45. WHEN 'R5' THEN ' ORDER BY m_MaximumUnity DESC,CreateTime'
      46. WHEN 'R6' THEN ' ORDER BY m_nGuildGold DESC,CreateTime'
      47. WHEN 'R7' THEN ' ORDER BY m_AvgLevel DESC,CreateTime'
      48. WHEN 'R8' THEN ' ORDER BY m_nPlayTime DESC,CreateTime' END
      50. EXEC
      51. (
      52. 'SELECT TOP 20 [order],Gu,s_date,serverindex,m_dwLogo,m_idGuild,m_szGuild,m_szName,
      53. m_nWin,m_nLose,m_nSurrender,m_MaximumUnity,m_AvgLevel,
      54. m_nGuildGold,m_nWinPoint,m_nPlayTime,CreateTime
      55. FROM RANKING_TBL
      56. WHERE Gu = ''' + @iGu + '''
      57. AND serverindex = ''' + @iserverindex + '''
      58. AND s_date = ''' + @os_date + '''' + @orderby
      59. )
      60. RETURN
      62. CREATE proc usp_guildbank_log_view
      63. @iGu char(2) = 'S1',
      64. @im_idGuild char(6) = '01',
      65. @iserverindex CHAR(2) = ''
      66. AS
      67. SET NOCOUNT ON
      69. declare @q1 nvarchar(4000)
      70. declare @q2 nvarchar(4000)
      72. IF @iGu = 'S1'
      73. BEGIN
      74. set @q1 = '
      75. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      76. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      77. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''A'' ORDER BY s_date DESC'
      78. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      79. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      80. END
      81. /*
      82. 넣기 A 빼기 D 돈넣기 I 돈빼기 O
      83. */
      84. ELSE
      85. IF @iGu = 'S2'
      86. BEGIN
      87. set @q1 = '
      88. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      89. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      90. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''D'' ORDER BY s_date DESC'
      91. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      92. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      93. END
      94. /*
      96. */
      97. ELSE
      98. IF @iGu = 'S3'
      99. BEGIN
      100. set @q1 = '
      101. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      102. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      103. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''I'' ORDER BY s_date DESC'
      104. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      105. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      106. END
      107. /*
      109. */
      110. ELSE
      111. IF @iGu = 'S4'
      112. BEGIN
      113. set @q1 = '
      114. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      115. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      116. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''O'' ORDER BY s_date DESC'
      117. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      118. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      119. END
      120. /*
      122. */
      123. 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:

      Quellcode

      1. CREATE PROC MAKE_RANKING_STR
      2. @iserverindex CHAR(2) = '01'
      3. AS
      4. set nocount on
      5. DECLARE @currDate char(10),@om_nCount INT,@of_nCount INT
      6. SET @currDate = CONVERT(CHAR(8),GETDATE(),112)
      7. + RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(hh,GETDATE())),2)
      8. -- + RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(mi,GETDATE())),2)
      11. --R1 : 최강길드
      12. --R2 : 최다승
      13. --R3 : 최다패
      14. --R4 : 최다항복패
      15. --R5 : 최고결속력
      16. --R6 : 최고자금
      17. --R7 : 평균고랩
      18. --R8 : 최대플레이
      20. --SELECT * FROM RANKING.RANKING_DBF.dbo.RANKING_TBL
      21. --DELETE RANKING.RANKING_DBF.dbo.RANKING_TBL
      22. --R1 : 최강길드
      24. IF EXISTS(SELECT * FROM RANKING.RANKING_DBF.dbo.RANKING_TBL WHERE s_date = @currDate and serverindex = @iserverindex)
      25. BEGIN
      26. DELETE RANKING.RANKING_DBF.dbo.RANKING_TBL WHERE s_date = @currDate and serverindex = @iserverindex
      27. END
      29. BEGIN
      30. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      31. (
      32. Gu, s_date,
      33. serverindex,
      34. m_dwLogo,
      35. m_idGuild,
      36. m_szGuild,
      37. m_szName,
      38. m_nWin,
      39. m_nLose,
      40. m_nSurrender,
      41. m_MaximumUnity,
      42. m_AvgLevel,
      43. m_nGuildGold,
      44. m_nWinPoint,
      45. m_nPlayTime,
      46. CreateTime
      47. )
      48. SELECT TOP 20 Gu = 'R1',s_date = @currDate,-- m_Title = '최강길드',
      49. serverindex = MAX(A.serverindex),
      50. m_dwLogo = MAX(A.m_dwLogo),
      51. B.m_idGuild,
      52. m_szGuild = MAX(A.m_szGuild),
      53. m_szName = MAX(C.m_szName),
      54. m_nWin = MAX(A.m_nWin),
      55. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      56. m_nSurrender = MAX(A.m_nSurrender),
      57. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      58. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      59. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      60. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      61. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      62. CreateTime = MAX(A.CreateTime)
      63. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      64. WHERE A.m_idGuild = B.m_idGuild
      65. AND A.serverindex = E.serverindex
      66. AND C.m_idPlayer = E.m_idPlayer
      67. AND B.m_nMemberLv = 0
      68. AND B.m_idPlayer = C.m_idPlayer
      69. AND A.serverindex = B.serverindex
      70. AND B.serverindex = C.serverindex
      71. AND C.serverindex = @iserverindex
      72. AND D.m_chLoginAuthority ='F'
      73. AND C.account = D.account
      74. GROUP BY B.m_idGuild
      75. ORDER BY m_nWinPoint DESC,m_nWin DESC
      79. --R2 : 최다승
      81. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      82. (
      83. Gu, s_date,
      84. serverindex,
      85. m_dwLogo,
      86. m_idGuild,
      87. m_szGuild,
      88. m_szName,
      89. m_nWin,
      90. m_nLose,
      91. m_nSurrender,
      92. m_MaximumUnity,
      93. m_AvgLevel,
      94. m_nGuildGold,
      95. m_nWinPoint,
      96. m_nPlayTime,
      97. CreateTime
      98. )
      99. SELECT TOP 20 Gu = 'R2',s_date = @currDate,--m_Title = '최다승',
      100. serverindex = MAX(A.serverindex),
      101. m_dwLogo = MAX(A.m_dwLogo),
      102. B.m_idGuild,
      103. m_szGuild = MAX(A.m_szGuild),
      104. m_szName = MAX(C.m_szName),
      105. m_nWin = MAX(A.m_nWin),
      106. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      107. m_nSurrender = MAX(A.m_nSurrender),
      108. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      109. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      110. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      111. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      112. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      113. CreateTime = MAX(A.CreateTime)
      114. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      115. WHERE A.m_idGuild = B.m_idGuild
      116. AND A.serverindex = E.serverindex
      117. AND C.m_idPlayer = E.m_idPlayer
      118. AND B.m_nMemberLv = 0
      119. AND B.m_idPlayer = C.m_idPlayer
      120. AND A.serverindex = B.serverindex
      121. AND B.serverindex = C.serverindex
      122. AND C.serverindex = @iserverindex
      123. AND D.m_chLoginAuthority = 'F'
      124. AND C.account = D.account
      125. GROUP BY B.m_idGuild
      126. ORDER BY m_nWin DESC,CreateTime
      128. --R3 : 최다패
      130. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      131. (
      132. Gu, s_date,
      133. serverindex,
      134. m_dwLogo,
      135. m_idGuild,
      136. m_szGuild,
      137. m_szName,
      138. m_nWin,
      139. m_nLose,
      140. m_nSurrender,
      141. m_MaximumUnity,
      142. m_AvgLevel,
      143. m_nGuildGold,
      144. m_nWinPoint,
      145. m_nPlayTime,
      146. CreateTime
      147. )
      148. SELECT TOP 20 Gu = 'R3',s_date = @currDate,-- m_Title = '최다패',
      149. serverindex = MAX(A.serverindex),
      150. m_dwLogo = MAX(A.m_dwLogo),
      151. B.m_idGuild,
      152. m_szGuild = MAX(A.m_szGuild),
      153. m_szName = MAX(C.m_szName),
      154. m_nWin = MAX(A.m_nWin),
      155. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      156. m_nSurrender = MAX(A.m_nSurrender),
      157. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      158. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      159. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      160. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      161. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      162. CreateTime = MAX(A.CreateTime)
      163. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      164. WHERE A.m_idGuild = B.m_idGuild
      165. AND A.serverindex = E.serverindex
      166. AND C.m_idPlayer = E.m_idPlayer
      167. AND B.m_nMemberLv = 0
      168. AND B.m_idPlayer = C.m_idPlayer
      169. AND A.serverindex = B.serverindex
      170. AND B.serverindex = C.serverindex
      171. AND C.serverindex = @iserverindex
      172. AND D.m_chLoginAuthority = 'F'
      173. AND C.account = D.account
      174. GROUP BY B.m_idGuild
      175. ORDER BY m_nLose DESC,m_nSurrender DESC
      177. --R4 : 최다항복패
      179. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      180. (
      181. Gu, s_date,
      182. serverindex,
      183. m_dwLogo,
      184. m_idGuild,
      185. m_szGuild,
      186. m_szName,
      187. m_nWin,
      188. m_nLose,
      189. m_nSurrender,
      190. m_MaximumUnity,
      191. m_AvgLevel,
      192. m_nGuildGold,
      193. m_nWinPoint,
      194. m_nPlayTime,
      195. CreateTime
      196. )
      197. SELECT TOP 20 Gu = 'R4',s_date = @currDate,-- m_Title = '최다항복패',
      198. serverindex = MAX(A.serverindex),
      199. m_dwLogo = MAX(A.m_dwLogo),
      200. B.m_idGuild,
      201. m_szGuild = MAX(A.m_szGuild),
      202. m_szName = MAX(C.m_szName),
      203. m_nWin = MAX(A.m_nWin),
      204. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      205. m_nSurrender = MAX(A.m_nSurrender),
      206. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      207. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      208. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      209. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      210. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      211. CreateTime = MAX(A.CreateTime)
      212. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      213. WHERE A.m_idGuild = B.m_idGuild
      214. AND A.serverindex = E.serverindex
      215. AND C.m_idPlayer = E.m_idPlayer
      216. AND B.m_nMemberLv = 0
      217. AND B.m_idPlayer = C.m_idPlayer
      218. AND A.serverindex = B.serverindex
      219. AND B.serverindex = C.serverindex
      220. AND C.serverindex = @iserverindex
      221. AND D.m_chLoginAuthority = 'F'
      222. AND C.account = D.account
      223. GROUP BY B.m_idGuild
      224. ORDER BY m_nSurrender DESC,m_nLose DESC
      226. --R5 : 최고결속력
      228. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      229. (
      230. Gu, s_date,
      231. serverindex,
      232. m_dwLogo,
      233. m_idGuild,
      234. m_szGuild,
      235. m_szName,
      236. m_nWin,
      237. m_nLose,
      238. m_nSurrender,
      239. m_MaximumUnity,
      240. m_AvgLevel,
      241. m_nGuildGold,
      242. m_nWinPoint,
      243. m_nPlayTime,
      244. CreateTime
      245. )
      246. SELECT TOP 20 Gu = 'R5',s_date = @currDate,-- m_Title = '최고결속력',
      247. serverindex = MAX(A.serverindex),
      248. m_dwLogo = MAX(A.m_dwLogo),
      249. B.m_idGuild,
      250. m_szGuild = MAX(A.m_szGuild),
      251. m_szName = MAX(C.m_szName),
      252. m_nWin = MAX(A.m_nWin),
      253. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      254. m_nSurrender = MAX(A.m_nSurrender),
      255. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      256. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      257. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      258. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      259. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      260. CreateTime = MAX(A.CreateTime)
      261. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      262. WHERE A.m_idGuild = B.m_idGuild
      263. AND A.serverindex = E.serverindex
      264. AND C.m_idPlayer = E.m_idPlayer
      265. AND B.m_nMemberLv = 0
      266. AND B.m_idPlayer = C.m_idPlayer
      267. AND A.serverindex = B.serverindex
      268. AND B.serverindex = C.serverindex
      269. AND C.serverindex = @iserverindex
      270. AND D.m_chLoginAuthority = 'F'
      271. AND C.account = D.account
      272. GROUP BY B.m_idGuild
      273. ORDER BY m_MaximumUnity DESC,CreateTime
      275. --R6 : 최고자금
      277. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      278. (
      279. Gu, s_date,
      280. serverindex,
      281. m_dwLogo,
      282. m_idGuild,
      283. m_szGuild,
      284. m_szName,
      285. m_nWin,
      286. m_nLose,
      287. m_nSurrender,
      288. m_MaximumUnity,
      289. m_AvgLevel,
      290. m_nGuildGold,
      291. m_nWinPoint,
      292. m_nPlayTime,
      293. CreateTime
      294. )
      295. select top 20 *
      296. from (
      297. SELECT TOP 20 Gu = 'R6', s_date = @currDate,-- m_Title = '최고자금',
      298. serverindex = MAX(A.serverindex),
      299. m_dwLogo = MAX(A.m_dwLogo),
      300. B.m_idGuild,
      301. m_szGuild = MAX(A.m_szGuild),
      302. m_szName = MAX(C.m_szName),
      303. m_nWin = MAX(A.m_nWin),
      304. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      305. m_nSurrender = MAX(A.m_nSurrender),
      306. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      307. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      308. m_nGuildGold = ISNULL(MAX(dbo.fn_GuildGold(A.m_nGuildGold)),0),
      309. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      310. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      311. CreateTime = MAX(A.CreateTime)
      312. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      313. WHERE A.m_idGuild = B.m_idGuild
      314. AND A.serverindex = E.serverindex
      315. AND C.m_idPlayer = E.m_idPlayer
      316. AND B.m_nMemberLv = 0
      317. AND B.m_idPlayer = C.m_idPlayer
      318. AND A.serverindex = B.serverindex
      319. AND B.serverindex = C.serverindex
      320. AND C.serverindex = @iserverindex
      321. AND D.m_chLoginAuthority = 'F'
      322. AND C.account = D.account
      323. and A.m_nGuildGold > 0
      324. GROUP BY B.m_idGuild
      325. ORDER BY m_nGuildGold DESC,CreateTime
      326. /*union all
      327. SELECT TOP 20 Gu = 'R6', s_date = @currDate,-- m_Title = '최고자금',
      328. serverindex = MAX(A.serverindex),
      329. m_dwLogo = MAX(A.m_dwLogo),
      330. B.m_idGuild,
      331. m_szGuild = MAX(A.m_szGuild),
      332. m_szName = MAX(C.m_szName),
      333. m_nWin = MAX(A.m_nWin),
      334. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      335. m_nSurrender = MAX(A.m_nSurrender),
      336. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      337. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      338. -- m_nGuildGold = ISNULL(MAX(A.m_nGuildGold + 4294967295),0),
      339. m_nGuildGold = isnull(max(dbo.fn_GuildGold(A.m_nGuildGold)), 0),
      340. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      341. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      342. CreateTime = MAX(A.CreateTime)
      343. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      344. WHERE A.m_idGuild = B.m_idGuild
      345. AND A.serverindex = E.serverindex
      346. AND C.m_idPlayer = E.m_idPlayer
      347. AND B.m_nMemberLv = 0
      348. AND B.m_idPlayer = C.m_idPlayer
      349. AND A.serverindex = B.serverindex
      350. AND B.serverindex = C.serverindex
      351. AND C.serverindex = @iserverindex
      352. AND D.m_chLoginAuthority = 'F'
      353. AND C.account = D.account
      354. and A.m_nGuildGold < 0
      355. GROUP BY B.m_idGuild
      356. ORDER BY A.m_nGuildGold DESC,CreateTime*/)x
      357. order by m_nGuildGold desc, CreateTime
      358. /*SELECT TOP 20 Gu = 'R6',s_date = @currDate,-- m_Title = '최고자금',
      359. serverindex = MAX(A.serverindex),
      360. m_dwLogo = MAX(A.m_dwLogo),
      361. B.m_idGuild,
      362. m_szGuild = MAX(A.m_szGuild),
      363. m_szName = MAX(C.m_szName),
      364. m_nWin = MAX(A.m_nWin),
      365. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      366. m_nSurrender = MAX(A.m_nSurrender),
      367. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      368. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      369. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      370. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      371. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      372. CreateTime = MAX(A.CreateTime)
      373. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      374. WHERE A.m_idGuild = B.m_idGuild
      375. AND A.serverindex = E.serverindex
      376. AND C.m_idPlayer = E.m_idPlayer
      377. AND B.m_nMemberLv = 0
      378. AND B.m_idPlayer = C.m_idPlayer
      379. AND A.serverindex = B.serverindex
      380. AND B.serverindex = C.serverindex
      381. AND C.serverindex = @iserverindex
      382. AND D.m_chLoginAuthority = 'F'
      383. AND C.account = D.account
      384. GROUP BY B.m_idGuild
      385. ORDER BY m_nGuildGold DESC,CreateTime
      386. */
      388. --R7 : 평균고랩
      390. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      391. (
      392. Gu, s_date,
      393. serverindex,
      394. m_dwLogo,
      395. m_idGuild,
      396. m_szGuild,
      397. m_szName,
      398. m_nWin,
      399. m_nLose,
      400. m_nSurrender,
      401. m_MaximumUnity,
      402. m_AvgLevel,
      403. m_nGuildGold,
      404. m_nWinPoint,
      405. m_nPlayTime,
      406. CreateTime
      407. )
      408. SELECT TOP 20 Gu = 'R7',s_date = @currDate,-- m_Title = '평균고랩',
      409. serverindex = MAX(A.serverindex),
      410. m_dwLogo = MAX(A.m_dwLogo),
      411. B.m_idGuild,
      412. m_szGuild = MAX(A.m_szGuild),
      413. m_szName = MAX(C.m_szName),
      414. m_nWin = MAX(A.m_nWin),
      415. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      416. m_nSurrender = MAX(A.m_nSurrender),
      417. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      418. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      419. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      420. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      421. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      422. CreateTime = MAX(A.CreateTime)
      423. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      424. WHERE A.m_idGuild = B.m_idGuild
      425. AND A.serverindex = E.serverindex
      426. AND C.m_idPlayer = E.m_idPlayer
      427. AND B.m_nMemberLv = 0
      428. AND B.m_idPlayer = C.m_idPlayer
      429. AND A.serverindex = B.serverindex
      430. AND B.serverindex = C.serverindex
      431. AND C.serverindex = @iserverindex
      432. AND D.m_chLoginAuthority = 'F'
      433. AND C.account = D.account
      434. GROUP BY B.m_idGuild
      435. ORDER BY m_AvgLevel DESC,CreateTime
      437. --R8 : 최대플레이
      439. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      440. (
      441. Gu, s_date,
      442. serverindex,
      443. m_dwLogo,
      444. m_idGuild,
      445. m_szGuild,
      446. m_szName,
      447. m_nWin,
      448. m_nLose,
      449. m_nSurrender,
      450. m_MaximumUnity,
      451. m_AvgLevel,
      452. m_nGuildGold,
      453. m_nWinPoint,
      454. m_nPlayTime,
      455. CreateTime
      456. )
      457. SELECT TOP 20 Gu = 'R8',s_date = @currDate,-- m_Title = '평균고랩',
      458. serverindex = MAX(A.serverindex),
      459. m_dwLogo = MAX(A.m_dwLogo),
      460. B.m_idGuild,
      461. m_szGuild = MAX(A.m_szGuild),
      462. m_szName = MAX(C.m_szName),
      463. m_nWin = MAX(A.m_nWin),
      464. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      465. m_nSurrender = MAX(A.m_nSurrender),
      466. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      467. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      468. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      469. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      470. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      471. CreateTime = MAX(A.CreateTime)
      472. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      473. WHERE A.m_idGuild = B.m_idGuild
      474. AND C.serverindex = E.serverindex
      475. AND C.m_idPlayer = E.m_idPlayer
      476. AND B.m_nMemberLv = 0
      477. AND B.m_idPlayer = C.m_idPlayer
      478. AND A.serverindex = B.serverindex
      479. AND B.serverindex = C.serverindex
      480. AND C.serverindex = @iserverindex
      481. AND D.m_chLoginAuthority = 'F'
      482. AND C.account = D.account
      483. GROUP BY B.m_idGuild
      484. ORDER BY m_nPlayTime DESC,CreateTime
      485. END
      Alles anzeigen

    • TwiLight;108369 schrieb:

      Nehmt doch gleich die Offi RankingDB.

      Quellcode

      1. CREATE TABLE [dbo].[RANKING_TBL] (
      2. [order] [int] NULL ,
      3. [order_all] [int] NULL ,
      4. [Gu] [char] (2) NOT NULL ,
      5. [s_date] [char] (10) NOT NULL ,
      6. [serverindex] [char] (2) NOT NULL ,
      7. [m_dwLogo] [int] NULL ,
      8. [m_idGuild] [char] (6) NOT NULL ,
      9. [m_szGuild] [varchar] (48) NULL ,
      10. [m_szName] [varchar] (32) NULL ,
      11. [m_nWin] [int] NULL ,
      12. [m_nLose] [int] NULL ,
      13. [m_nSurrender] [int] NULL ,
      14. [m_MaximumUnity] [float] NULL ,
      15. [m_AvgLevel] [float] NULL ,
      16. [m_nGuildGold] [bigint] NULL ,
      17. [m_nWinPoint] [int] NULL ,
      18. [m_nPlayTime] [int] NULL ,
      19. [CreateTime] [datetime] NULL
      20. ) ON [PRIMARY]
      21. GO
      23. CREATE PROC RANKING_STR
      24. @iGu CHAR(2) = 'R1',
      25. @iserverindex CHAR(2) = '01'
      26. AS
      27. DECLARE @os_date CHAR(10)
      28. SELECT @os_date = MAX(s_date) FROM RANKING_TBL WHERE Gu = @iGu AND serverindex = @iserverindex
      30. DECLARE @orderby VARCHAR(255)
      32. --R1 : 최강길드
      33. --R2 : 최다승
      34. --R3 : 최다패
      35. --R4 : 최다항복패
      36. --R5 : 최고결속력
      37. --R6 : 최고자금
      38. --R7 : 평균고랩
      39. --R8 : 최대플레이
      41. SELECT @orderby = CASE @iGu WHEN 'R1' THEN ' ORDER BY m_nWinPoint DESC,m_nWin DESC'
      42. WHEN 'R2' THEN ' ORDER BY m_nWin DESC,CreateTime'
      43. WHEN 'R3' THEN ' ORDER BY m_nLose DESC,m_nSurrender DESC'
      44. WHEN 'R4' THEN ' ORDER BY m_nSurrender DESC,m_nLose DESC'
      45. WHEN 'R5' THEN ' ORDER BY m_MaximumUnity DESC,CreateTime'
      46. WHEN 'R6' THEN ' ORDER BY m_nGuildGold DESC,CreateTime'
      47. WHEN 'R7' THEN ' ORDER BY m_AvgLevel DESC,CreateTime'
      48. WHEN 'R8' THEN ' ORDER BY m_nPlayTime DESC,CreateTime' END
      50. EXEC
      51. (
      52. 'SELECT TOP 20 [order],Gu,s_date,serverindex,m_dwLogo,m_idGuild,m_szGuild,m_szName,
      53. m_nWin,m_nLose,m_nSurrender,m_MaximumUnity,m_AvgLevel,
      54. m_nGuildGold,m_nWinPoint,m_nPlayTime,CreateTime
      55. FROM RANKING_TBL
      56. WHERE Gu = ''' + @iGu + '''
      57. AND serverindex = ''' + @iserverindex + '''
      58. AND s_date = ''' + @os_date + '''' + @orderby
      59. )
      60. RETURN
      62. CREATE proc usp_guildbank_log_view
      63. @iGu char(2) = 'S1',
      64. @im_idGuild char(6) = '01',
      65. @iserverindex CHAR(2) = ''
      66. AS
      67. SET NOCOUNT ON
      69. declare @q1 nvarchar(4000)
      70. declare @q2 nvarchar(4000)
      72. IF @iGu = 'S1'
      73. BEGIN
      74. set @q1 = '
      75. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      76. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      77. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''A'' ORDER BY s_date DESC'
      78. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      79. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      80. END
      81. /*
      82. 넣기 A 빼기 D 돈넣기 I 돈빼기 O
      83. */
      84. ELSE
      85. IF @iGu = 'S2'
      86. BEGIN
      87. set @q1 = '
      88. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      89. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      90. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''D'' ORDER BY s_date DESC'
      91. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      92. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      93. END
      94. /*
      96. */
      97. ELSE
      98. IF @iGu = 'S3'
      99. BEGIN
      100. set @q1 = '
      101. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      102. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      103. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''I'' ORDER BY s_date DESC'
      104. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      105. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      106. END
      107. /*
      109. */
      110. ELSE
      111. IF @iGu = 'S4'
      112. BEGIN
      113. set @q1 = '
      114. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      115. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      116. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''O'' ORDER BY s_date DESC'
      117. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      118. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      119. END
      120. /*
      122. */
      123. 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:

      Quellcode

      1. CREATE PROC MAKE_RANKING_STR
      2. @iserverindex CHAR(2) = '01'
      3. AS
      4. set nocount on
      5. DECLARE @currDate char(10),@om_nCount INT,@of_nCount INT
      6. SET @currDate = CONVERT(CHAR(8),GETDATE(),112)
      7. + RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(hh,GETDATE())),2)
      8. -- + RIGHT('00' + CONVERT(VARCHAR(2),DATEPART(mi,GETDATE())),2)
      11. --R1 : 최강길드
      12. --R2 : 최다승
      13. --R3 : 최다패
      14. --R4 : 최다항복패
      15. --R5 : 최고결속력
      16. --R6 : 최고자금
      17. --R7 : 평균고랩
      18. --R8 : 최대플레이
      20. --SELECT * FROM RANKING.RANKING_DBF.dbo.RANKING_TBL
      21. --DELETE RANKING.RANKING_DBF.dbo.RANKING_TBL
      22. --R1 : 최강길드
      24. IF EXISTS(SELECT * FROM RANKING.RANKING_DBF.dbo.RANKING_TBL WHERE s_date = @currDate and serverindex = @iserverindex)
      25. BEGIN
      26. DELETE RANKING.RANKING_DBF.dbo.RANKING_TBL WHERE s_date = @currDate and serverindex = @iserverindex
      27. END
      29. BEGIN
      30. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      31. (
      32. Gu, s_date,
      33. serverindex,
      34. m_dwLogo,
      35. m_idGuild,
      36. m_szGuild,
      37. m_szName,
      38. m_nWin,
      39. m_nLose,
      40. m_nSurrender,
      41. m_MaximumUnity,
      42. m_AvgLevel,
      43. m_nGuildGold,
      44. m_nWinPoint,
      45. m_nPlayTime,
      46. CreateTime
      47. )
      48. SELECT TOP 20 Gu = 'R1',s_date = @currDate,-- m_Title = '최강길드',
      49. serverindex = MAX(A.serverindex),
      50. m_dwLogo = MAX(A.m_dwLogo),
      51. B.m_idGuild,
      52. m_szGuild = MAX(A.m_szGuild),
      53. m_szName = MAX(C.m_szName),
      54. m_nWin = MAX(A.m_nWin),
      55. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      56. m_nSurrender = MAX(A.m_nSurrender),
      57. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      58. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      59. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      60. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      61. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      62. CreateTime = MAX(A.CreateTime)
      63. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      64. WHERE A.m_idGuild = B.m_idGuild
      65. AND A.serverindex = E.serverindex
      66. AND C.m_idPlayer = E.m_idPlayer
      67. AND B.m_nMemberLv = 0
      68. AND B.m_idPlayer = C.m_idPlayer
      69. AND A.serverindex = B.serverindex
      70. AND B.serverindex = C.serverindex
      71. AND C.serverindex = @iserverindex
      72. AND D.m_chLoginAuthority ='F'
      73. AND C.account = D.account
      74. GROUP BY B.m_idGuild
      75. ORDER BY m_nWinPoint DESC,m_nWin DESC
      79. --R2 : 최다승
      81. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      82. (
      83. Gu, s_date,
      84. serverindex,
      85. m_dwLogo,
      86. m_idGuild,
      87. m_szGuild,
      88. m_szName,
      89. m_nWin,
      90. m_nLose,
      91. m_nSurrender,
      92. m_MaximumUnity,
      93. m_AvgLevel,
      94. m_nGuildGold,
      95. m_nWinPoint,
      96. m_nPlayTime,
      97. CreateTime
      98. )
      99. SELECT TOP 20 Gu = 'R2',s_date = @currDate,--m_Title = '최다승',
      100. serverindex = MAX(A.serverindex),
      101. m_dwLogo = MAX(A.m_dwLogo),
      102. B.m_idGuild,
      103. m_szGuild = MAX(A.m_szGuild),
      104. m_szName = MAX(C.m_szName),
      105. m_nWin = MAX(A.m_nWin),
      106. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      107. m_nSurrender = MAX(A.m_nSurrender),
      108. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      109. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      110. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      111. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      112. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      113. CreateTime = MAX(A.CreateTime)
      114. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      115. WHERE A.m_idGuild = B.m_idGuild
      116. AND A.serverindex = E.serverindex
      117. AND C.m_idPlayer = E.m_idPlayer
      118. AND B.m_nMemberLv = 0
      119. AND B.m_idPlayer = C.m_idPlayer
      120. AND A.serverindex = B.serverindex
      121. AND B.serverindex = C.serverindex
      122. AND C.serverindex = @iserverindex
      123. AND D.m_chLoginAuthority = 'F'
      124. AND C.account = D.account
      125. GROUP BY B.m_idGuild
      126. ORDER BY m_nWin DESC,CreateTime
      128. --R3 : 최다패
      130. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      131. (
      132. Gu, s_date,
      133. serverindex,
      134. m_dwLogo,
      135. m_idGuild,
      136. m_szGuild,
      137. m_szName,
      138. m_nWin,
      139. m_nLose,
      140. m_nSurrender,
      141. m_MaximumUnity,
      142. m_AvgLevel,
      143. m_nGuildGold,
      144. m_nWinPoint,
      145. m_nPlayTime,
      146. CreateTime
      147. )
      148. SELECT TOP 20 Gu = 'R3',s_date = @currDate,-- m_Title = '최다패',
      149. serverindex = MAX(A.serverindex),
      150. m_dwLogo = MAX(A.m_dwLogo),
      151. B.m_idGuild,
      152. m_szGuild = MAX(A.m_szGuild),
      153. m_szName = MAX(C.m_szName),
      154. m_nWin = MAX(A.m_nWin),
      155. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      156. m_nSurrender = MAX(A.m_nSurrender),
      157. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      158. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      159. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      160. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      161. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      162. CreateTime = MAX(A.CreateTime)
      163. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      164. WHERE A.m_idGuild = B.m_idGuild
      165. AND A.serverindex = E.serverindex
      166. AND C.m_idPlayer = E.m_idPlayer
      167. AND B.m_nMemberLv = 0
      168. AND B.m_idPlayer = C.m_idPlayer
      169. AND A.serverindex = B.serverindex
      170. AND B.serverindex = C.serverindex
      171. AND C.serverindex = @iserverindex
      172. AND D.m_chLoginAuthority = 'F'
      173. AND C.account = D.account
      174. GROUP BY B.m_idGuild
      175. ORDER BY m_nLose DESC,m_nSurrender DESC
      177. --R4 : 최다항복패
      179. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      180. (
      181. Gu, s_date,
      182. serverindex,
      183. m_dwLogo,
      184. m_idGuild,
      185. m_szGuild,
      186. m_szName,
      187. m_nWin,
      188. m_nLose,
      189. m_nSurrender,
      190. m_MaximumUnity,
      191. m_AvgLevel,
      192. m_nGuildGold,
      193. m_nWinPoint,
      194. m_nPlayTime,
      195. CreateTime
      196. )
      197. SELECT TOP 20 Gu = 'R4',s_date = @currDate,-- m_Title = '최다항복패',
      198. serverindex = MAX(A.serverindex),
      199. m_dwLogo = MAX(A.m_dwLogo),
      200. B.m_idGuild,
      201. m_szGuild = MAX(A.m_szGuild),
      202. m_szName = MAX(C.m_szName),
      203. m_nWin = MAX(A.m_nWin),
      204. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      205. m_nSurrender = MAX(A.m_nSurrender),
      206. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      207. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      208. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      209. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      210. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      211. CreateTime = MAX(A.CreateTime)
      212. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      213. WHERE A.m_idGuild = B.m_idGuild
      214. AND A.serverindex = E.serverindex
      215. AND C.m_idPlayer = E.m_idPlayer
      216. AND B.m_nMemberLv = 0
      217. AND B.m_idPlayer = C.m_idPlayer
      218. AND A.serverindex = B.serverindex
      219. AND B.serverindex = C.serverindex
      220. AND C.serverindex = @iserverindex
      221. AND D.m_chLoginAuthority = 'F'
      222. AND C.account = D.account
      223. GROUP BY B.m_idGuild
      224. ORDER BY m_nSurrender DESC,m_nLose DESC
      226. --R5 : 최고결속력
      228. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      229. (
      230. Gu, s_date,
      231. serverindex,
      232. m_dwLogo,
      233. m_idGuild,
      234. m_szGuild,
      235. m_szName,
      236. m_nWin,
      237. m_nLose,
      238. m_nSurrender,
      239. m_MaximumUnity,
      240. m_AvgLevel,
      241. m_nGuildGold,
      242. m_nWinPoint,
      243. m_nPlayTime,
      244. CreateTime
      245. )
      246. SELECT TOP 20 Gu = 'R5',s_date = @currDate,-- m_Title = '최고결속력',
      247. serverindex = MAX(A.serverindex),
      248. m_dwLogo = MAX(A.m_dwLogo),
      249. B.m_idGuild,
      250. m_szGuild = MAX(A.m_szGuild),
      251. m_szName = MAX(C.m_szName),
      252. m_nWin = MAX(A.m_nWin),
      253. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      254. m_nSurrender = MAX(A.m_nSurrender),
      255. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      256. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      257. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      258. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      259. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      260. CreateTime = MAX(A.CreateTime)
      261. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      262. WHERE A.m_idGuild = B.m_idGuild
      263. AND A.serverindex = E.serverindex
      264. AND C.m_idPlayer = E.m_idPlayer
      265. AND B.m_nMemberLv = 0
      266. AND B.m_idPlayer = C.m_idPlayer
      267. AND A.serverindex = B.serverindex
      268. AND B.serverindex = C.serverindex
      269. AND C.serverindex = @iserverindex
      270. AND D.m_chLoginAuthority = 'F'
      271. AND C.account = D.account
      272. GROUP BY B.m_idGuild
      273. ORDER BY m_MaximumUnity DESC,CreateTime
      275. --R6 : 최고자금
      277. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      278. (
      279. Gu, s_date,
      280. serverindex,
      281. m_dwLogo,
      282. m_idGuild,
      283. m_szGuild,
      284. m_szName,
      285. m_nWin,
      286. m_nLose,
      287. m_nSurrender,
      288. m_MaximumUnity,
      289. m_AvgLevel,
      290. m_nGuildGold,
      291. m_nWinPoint,
      292. m_nPlayTime,
      293. CreateTime
      294. )
      295. select top 20 *
      296. from (
      297. SELECT TOP 20 Gu = 'R6', s_date = @currDate,-- m_Title = '최고자금',
      298. serverindex = MAX(A.serverindex),
      299. m_dwLogo = MAX(A.m_dwLogo),
      300. B.m_idGuild,
      301. m_szGuild = MAX(A.m_szGuild),
      302. m_szName = MAX(C.m_szName),
      303. m_nWin = MAX(A.m_nWin),
      304. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      305. m_nSurrender = MAX(A.m_nSurrender),
      306. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      307. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      308. m_nGuildGold = ISNULL(MAX(dbo.fn_GuildGold(A.m_nGuildGold)),0),
      309. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      310. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      311. CreateTime = MAX(A.CreateTime)
      312. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      313. WHERE A.m_idGuild = B.m_idGuild
      314. AND A.serverindex = E.serverindex
      315. AND C.m_idPlayer = E.m_idPlayer
      316. AND B.m_nMemberLv = 0
      317. AND B.m_idPlayer = C.m_idPlayer
      318. AND A.serverindex = B.serverindex
      319. AND B.serverindex = C.serverindex
      320. AND C.serverindex = @iserverindex
      321. AND D.m_chLoginAuthority = 'F'
      322. AND C.account = D.account
      323. and A.m_nGuildGold > 0
      324. GROUP BY B.m_idGuild
      325. ORDER BY m_nGuildGold DESC,CreateTime
      326. /*union all
      327. SELECT TOP 20 Gu = 'R6', s_date = @currDate,-- m_Title = '최고자금',
      328. serverindex = MAX(A.serverindex),
      329. m_dwLogo = MAX(A.m_dwLogo),
      330. B.m_idGuild,
      331. m_szGuild = MAX(A.m_szGuild),
      332. m_szName = MAX(C.m_szName),
      333. m_nWin = MAX(A.m_nWin),
      334. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      335. m_nSurrender = MAX(A.m_nSurrender),
      336. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      337. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      338. -- m_nGuildGold = ISNULL(MAX(A.m_nGuildGold + 4294967295),0),
      339. m_nGuildGold = isnull(max(dbo.fn_GuildGold(A.m_nGuildGold)), 0),
      340. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      341. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      342. CreateTime = MAX(A.CreateTime)
      343. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      344. WHERE A.m_idGuild = B.m_idGuild
      345. AND A.serverindex = E.serverindex
      346. AND C.m_idPlayer = E.m_idPlayer
      347. AND B.m_nMemberLv = 0
      348. AND B.m_idPlayer = C.m_idPlayer
      349. AND A.serverindex = B.serverindex
      350. AND B.serverindex = C.serverindex
      351. AND C.serverindex = @iserverindex
      352. AND D.m_chLoginAuthority = 'F'
      353. AND C.account = D.account
      354. and A.m_nGuildGold < 0
      355. GROUP BY B.m_idGuild
      356. ORDER BY A.m_nGuildGold DESC,CreateTime*/)x
      357. order by m_nGuildGold desc, CreateTime
      358. /*SELECT TOP 20 Gu = 'R6',s_date = @currDate,-- m_Title = '최고자금',
      359. serverindex = MAX(A.serverindex),
      360. m_dwLogo = MAX(A.m_dwLogo),
      361. B.m_idGuild,
      362. m_szGuild = MAX(A.m_szGuild),
      363. m_szName = MAX(C.m_szName),
      364. m_nWin = MAX(A.m_nWin),
      365. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      366. m_nSurrender = MAX(A.m_nSurrender),
      367. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      368. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      369. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      370. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      371. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      372. CreateTime = MAX(A.CreateTime)
      373. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      374. WHERE A.m_idGuild = B.m_idGuild
      375. AND A.serverindex = E.serverindex
      376. AND C.m_idPlayer = E.m_idPlayer
      377. AND B.m_nMemberLv = 0
      378. AND B.m_idPlayer = C.m_idPlayer
      379. AND A.serverindex = B.serverindex
      380. AND B.serverindex = C.serverindex
      381. AND C.serverindex = @iserverindex
      382. AND D.m_chLoginAuthority = 'F'
      383. AND C.account = D.account
      384. GROUP BY B.m_idGuild
      385. ORDER BY m_nGuildGold DESC,CreateTime
      386. */
      388. --R7 : 평균고랩
      390. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      391. (
      392. Gu, s_date,
      393. serverindex,
      394. m_dwLogo,
      395. m_idGuild,
      396. m_szGuild,
      397. m_szName,
      398. m_nWin,
      399. m_nLose,
      400. m_nSurrender,
      401. m_MaximumUnity,
      402. m_AvgLevel,
      403. m_nGuildGold,
      404. m_nWinPoint,
      405. m_nPlayTime,
      406. CreateTime
      407. )
      408. SELECT TOP 20 Gu = 'R7',s_date = @currDate,-- m_Title = '평균고랩',
      409. serverindex = MAX(A.serverindex),
      410. m_dwLogo = MAX(A.m_dwLogo),
      411. B.m_idGuild,
      412. m_szGuild = MAX(A.m_szGuild),
      413. m_szName = MAX(C.m_szName),
      414. m_nWin = MAX(A.m_nWin),
      415. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      416. m_nSurrender = MAX(A.m_nSurrender),
      417. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      418. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      419. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      420. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      421. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      422. CreateTime = MAX(A.CreateTime)
      423. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      424. WHERE A.m_idGuild = B.m_idGuild
      425. AND A.serverindex = E.serverindex
      426. AND C.m_idPlayer = E.m_idPlayer
      427. AND B.m_nMemberLv = 0
      428. AND B.m_idPlayer = C.m_idPlayer
      429. AND A.serverindex = B.serverindex
      430. AND B.serverindex = C.serverindex
      431. AND C.serverindex = @iserverindex
      432. AND D.m_chLoginAuthority = 'F'
      433. AND C.account = D.account
      434. GROUP BY B.m_idGuild
      435. ORDER BY m_AvgLevel DESC,CreateTime
      437. --R8 : 최대플레이
      439. INSERT RANKING.RANKING_DBF.dbo.RANKING_TBL
      440. (
      441. Gu, s_date,
      442. serverindex,
      443. m_dwLogo,
      444. m_idGuild,
      445. m_szGuild,
      446. m_szName,
      447. m_nWin,
      448. m_nLose,
      449. m_nSurrender,
      450. m_MaximumUnity,
      451. m_AvgLevel,
      452. m_nGuildGold,
      453. m_nWinPoint,
      454. m_nPlayTime,
      455. CreateTime
      456. )
      457. SELECT TOP 20 Gu = 'R8',s_date = @currDate,-- m_Title = '평균고랩',
      458. serverindex = MAX(A.serverindex),
      459. m_dwLogo = MAX(A.m_dwLogo),
      460. B.m_idGuild,
      461. m_szGuild = MAX(A.m_szGuild),
      462. m_szName = MAX(C.m_szName),
      463. m_nWin = MAX(A.m_nWin),
      464. m_nLose = MAX(A.m_nLose)+ MAX(A.m_nSurrender),
      465. m_nSurrender = MAX(A.m_nSurrender),
      466. m_MaximumUnity = CONVERT(REAL,MAX(A.m_nWin)-(COUNT(B.m_nSurrender)/MAX(A.m_nLevel))),
      467. m_AvgLevel = ISNULL(AVG(E.m_nLevel),0),
      468. m_nGuildGold = ISNULL(MAX(A.m_nGuildGold),0),
      469. m_nWinPoint = ISNULL(MAX(A.m_nWinPoint),0),
      470. m_nPlayTime = ISNULL(SUM(E.TotalPlayTime),0),
      471. CreateTime = MAX(A.CreateTime)
      472. FROM GUILD_TBL A,GUILD_MEMBER_TBL B,CHARACTER_TBL C,ACCOUNT.ACCOUNT_DBF.dbo.ACCOUNT_TBL_DETAIL D,CHARACTER_TBL E
      473. WHERE A.m_idGuild = B.m_idGuild
      474. AND C.serverindex = E.serverindex
      475. AND C.m_idPlayer = E.m_idPlayer
      476. AND B.m_nMemberLv = 0
      477. AND B.m_idPlayer = C.m_idPlayer
      478. AND A.serverindex = B.serverindex
      479. AND B.serverindex = C.serverindex
      480. AND C.serverindex = @iserverindex
      481. AND D.m_chLoginAuthority = 'F'
      482. AND C.account = D.account
      483. GROUP BY B.m_idGuild
      484. ORDER BY m_nPlayTime DESC,CreateTime
      485. END
      Alles anzeigen


      Bekomme hier einen Error:
      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.


      @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

    • 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 ;)

    • Valron;110059 schrieb:

      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].

      Quellcode

      1. CREATE TABLE [dbo].[RANKING_TBL] (
      2. [order] [int] NULL ,
      3. [order_all] [int] NULL ,
      4. [Gu] [char] (2) NOT NULL ,
      5. [s_date] [char] (10) NOT NULL ,
      6. [serverindex] [char] (2) NOT NULL ,
      7. [m_dwLogo] [int] NULL ,
      8. [m_idGuild] [char] (6) NOT NULL ,
      9. [m_szGuild] [varchar] (48) NULL ,
      10. [m_szName] [varchar] (32) NULL ,
      11. [m_nWin] [int] NULL ,
      12. [m_nLose] [int] NULL ,
      13. [m_nSurrender] [int] NULL ,
      14. [m_MaximumUnity] [float] NULL ,
      15. [m_AvgLevel] [float] NULL ,
      16. [m_nGuildGold] [bigint] NULL ,
      17. [m_nWinPoint] [int] NULL ,
      18. [m_nPlayTime] [int] NULL ,
      19. [CreateTime] [datetime] NULL
      20. ) ON [PRIMARY]
      21. GO
      23. [COLOR="Red"]SET QUOTED_IDENTIFIER ON
      24. GO[/COLOR]
      25. SET ANSI_NULLS ON
      26. GO
      28. CREATE PROC RANKING_STR
      29. @iGu CHAR(2) = 'R1',
      30. @iserverindex CHAR(2) = '01'
      31. AS
      32. DECLARE @os_date CHAR(10)
      33. SELECT @os_date = MAX(s_date) FROM RANKING_TBL WHERE Gu = @iGu AND serverindex = @iserverindex
      35. DECLARE @orderby VARCHAR(255)
      37. --R1 : 최강길드
      38. --R2 : 최다승
      39. --R3 : 최다패
      40. --R4 : 최다항복패
      41. --R5 : 최고결속력
      42. --R6 : 최고자금
      43. --R7 : 평균고랩
      44. --R8 : 최대플레이
      46. SELECT @orderby = CASE @iGu WHEN 'R1' THEN ' ORDER BY m_nWinPoint DESC,m_nWin DESC'
      47. WHEN 'R2' THEN ' ORDER BY m_nWin DESC,CreateTime'
      48. WHEN 'R3' THEN ' ORDER BY m_nLose DESC,m_nSurrender DESC'
      49. WHEN 'R4' THEN ' ORDER BY m_nSurrender DESC,m_nLose DESC'
      50. WHEN 'R5' THEN ' ORDER BY m_MaximumUnity DESC,CreateTime'
      51. WHEN 'R6' THEN ' ORDER BY m_nGuildGold DESC,CreateTime'
      52. WHEN 'R7' THEN ' ORDER BY m_AvgLevel DESC,CreateTime'
      53. WHEN 'R8' THEN ' ORDER BY m_nPlayTime DESC,CreateTime' END
      55. EXEC
      56. (
      57. 'SELECT TOP 20 [order],Gu,s_date,serverindex,m_dwLogo,m_idGuild,m_szGuild,m_szName,
      58. m_nWin,m_nLose,m_nSurrender,m_MaximumUnity,m_AvgLevel,
      59. m_nGuildGold,m_nWinPoint,m_nPlayTime,CreateTime
      60. FROM RANKING_TBL
      61. WHERE Gu = ''' + @iGu + '''
      62. AND serverindex = ''' + @iserverindex + '''
      63. AND s_date = ''' + @os_date + '''' + @orderby
      64. )
      65. RETURN
      67. GO
      68. [COLOR="Red"]SET QUOTED_IDENTIFIER OFF
      69. GO[/COLOR]
      70. SET ANSI_NULLS ON
      71. GO
      73. [COLOR="Red"]SET QUOTED_IDENTIFIER ON
      74. GO[/COLOR]
      75. SET ANSI_NULLS ON
      76. GO
      78. CREATE proc usp_guildbank_log_view
      79. @iGu char(2) = 'S1',
      80. @im_idGuild char(6) = '01',
      81. @iserverindex CHAR(2) = ''
      82. AS
      83. SET NOCOUNT ON
      85. declare @q1 nvarchar(4000)
      86. declare @q2 nvarchar(4000)
      88. IF @iGu = 'S1'
      89. BEGIN
      90. set @q1 = '
      91. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      92. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      93. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''A'' ORDER BY s_date DESC'
      94. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      95. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      96. END
      97. /*
      98. 넣기 A 빼기 D 돈넣기 I 돈빼기 O
      99. */
      100. ELSE
      101. IF @iGu = 'S2'
      102. BEGIN
      103. set @q1 = '
      104. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      105. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      106. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''D'' ORDER BY s_date DESC'
      107. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      108. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      109. END
      110. /*
      112. */
      113. ELSE
      114. IF @iGu = 'S3'
      115. BEGIN
      116. set @q1 = '
      117. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      118. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      119. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''I'' ORDER BY s_date DESC'
      120. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      121. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      122. END
      123. /*
      125. */
      126. ELSE
      127. IF @iGu = 'S4'
      128. BEGIN
      129. set @q1 = '
      130. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      131. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      132. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''O'' ORDER BY s_date DESC'
      133. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      134. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      135. END
      136. /*
      138. */
      139. RETURN
      140. GO
      141. [COLOR="Red"]SET QUOTED_IDENTIFIER OFF
      142. GO[/COLOR]
      143. SET ANSI_NULLS ON
      144. GO
      Alles anzeigen

    • Chaosduckman;110088 schrieb:

      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 :/


      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.

    • TwiLight;110079 schrieb:

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

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

      Quellcode

      1. CREATE TABLE [dbo].[RANKING_TBL] (
      2. [order] [int] NULL ,
      3. [order_all] [int] NULL ,
      4. [Gu] [char] (2) NOT NULL ,
      5. [s_date] [char] (10) NOT NULL ,
      6. [serverindex] [char] (2) NOT NULL ,
      7. [m_dwLogo] [int] NULL ,
      8. [m_idGuild] [char] (6) NOT NULL ,
      9. [m_szGuild] [varchar] (48) NULL ,
      10. [m_szName] [varchar] (32) NULL ,
      11. [m_nWin] [int] NULL ,
      12. [m_nLose] [int] NULL ,
      13. [m_nSurrender] [int] NULL ,
      14. [m_MaximumUnity] [float] NULL ,
      15. [m_AvgLevel] [float] NULL ,
      16. [m_nGuildGold] [bigint] NULL ,
      17. [m_nWinPoint] [int] NULL ,
      18. [m_nPlayTime] [int] NULL ,
      19. [CreateTime] [datetime] NULL
      20. ) ON [PRIMARY]
      21. GO
      23. [COLOR="Red"]SET QUOTED_IDENTIFIER ON
      24. GO[/COLOR]
      25. SET ANSI_NULLS ON
      26. GO
      28. CREATE PROC RANKING_STR
      29. @iGu CHAR(2) = 'R1',
      30. @iserverindex CHAR(2) = '01'
      31. AS
      32. DECLARE @os_date CHAR(10)
      33. SELECT @os_date = MAX(s_date) FROM RANKING_TBL WHERE Gu = @iGu AND serverindex = @iserverindex
      35. DECLARE @orderby VARCHAR(255)
      37. --R1 : 최강길드
      38. --R2 : 최다승
      39. --R3 : 최다패
      40. --R4 : 최다항복패
      41. --R5 : 최고결속력
      42. --R6 : 최고자금
      43. --R7 : 평균고랩
      44. --R8 : 최대플레이
      46. SELECT @orderby = CASE @iGu WHEN 'R1' THEN ' ORDER BY m_nWinPoint DESC,m_nWin DESC'
      47. WHEN 'R2' THEN ' ORDER BY m_nWin DESC,CreateTime'
      48. WHEN 'R3' THEN ' ORDER BY m_nLose DESC,m_nSurrender DESC'
      49. WHEN 'R4' THEN ' ORDER BY m_nSurrender DESC,m_nLose DESC'
      50. WHEN 'R5' THEN ' ORDER BY m_MaximumUnity DESC,CreateTime'
      51. WHEN 'R6' THEN ' ORDER BY m_nGuildGold DESC,CreateTime'
      52. WHEN 'R7' THEN ' ORDER BY m_AvgLevel DESC,CreateTime'
      53. WHEN 'R8' THEN ' ORDER BY m_nPlayTime DESC,CreateTime' END
      55. EXEC
      56. (
      57. 'SELECT TOP 20 [order],Gu,s_date,serverindex,m_dwLogo,m_idGuild,m_szGuild,m_szName,
      58. m_nWin,m_nLose,m_nSurrender,m_MaximumUnity,m_AvgLevel,
      59. m_nGuildGold,m_nWinPoint,m_nPlayTime,CreateTime
      60. FROM RANKING_TBL
      61. WHERE Gu = ''' + @iGu + '''
      62. AND serverindex = ''' + @iserverindex + '''
      63. AND s_date = ''' + @os_date + '''' + @orderby
      64. )
      65. RETURN
      67. GO
      68. [COLOR="Red"]SET QUOTED_IDENTIFIER OFF
      69. GO[/COLOR]
      70. SET ANSI_NULLS ON
      71. GO
      73. [COLOR="Red"]SET QUOTED_IDENTIFIER ON
      74. GO[/COLOR]
      75. SET ANSI_NULLS ON
      76. GO
      78. CREATE proc usp_guildbank_log_view
      79. @iGu char(2) = 'S1',
      80. @im_idGuild char(6) = '01',
      81. @iserverindex CHAR(2) = ''
      82. AS
      83. SET NOCOUNT ON
      85. declare @q1 nvarchar(4000)
      86. declare @q2 nvarchar(4000)
      88. IF @iGu = 'S1'
      89. BEGIN
      90. set @q1 = '
      91. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      92. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      93. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''A'' ORDER BY s_date DESC'
      94. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      95. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      96. END
      97. /*
      98. 넣기 A 빼기 D 돈넣기 I 돈빼기 O
      99. */
      100. ELSE
      101. IF @iGu = 'S2'
      102. BEGIN
      103. set @q1 = '
      104. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      105. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      106. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''D'' ORDER BY s_date DESC'
      107. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      108. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      109. END
      110. /*
      112. */
      113. ELSE
      114. IF @iGu = 'S3'
      115. BEGIN
      116. set @q1 = '
      117. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      118. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      119. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''I'' ORDER BY s_date DESC'
      120. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      121. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      122. END
      123. /*
      125. */
      126. ELSE
      127. IF @iGu = 'S4'
      128. BEGIN
      129. set @q1 = '
      130. SELECT TOP 100 m_idPlayer,s_date,m_Item,m_nAbilityOption,Item_count
      131. FROM LOGGING_[&server&]_DBF.dbo.LOG_GUILD_BANK_TBL (nolock)
      132. WHERE m_idGuild = @im_idGuild AND serverindex = @iserverindex AND State=''O'' ORDER BY s_date DESC'
      133. set @q2 = replace(@q1, '[&server&]', @iserverindex)
      134. exec sp_executesql @q2, N'@im_idGuild char(6), @iserverindex char(2)', @im_idGuild, @iserverindex
      135. END
      136. /*
      138. */
      139. RETURN
      140. GO
      141. [COLOR="Red"]SET QUOTED_IDENTIFIER OFF
      142. GO[/COLOR]
      143. SET ANSI_NULLS ON
      144. GO
      Alles anzeigen


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