1 -- =============================================  2 -- Author:        
      <华仔>
         3 -- Create date: <2016,6,22>  4 -- Description:    
       <玩家游戏财富日志>
          5 -- EXEC pr_GameScoreInfoChangeDetail '3','2016-06-22','2016-06-23','-999','CatchFish','96648' -- 捕鱼  6 -- EXEC pr_GameScoreInfoChangeDetail '3','2016-06-22','2016-06-23','-999','CowCow','全部' -- 牛牛  7 -- =============================================  8 ALTER PROCEDURE [dbo].[pr_GameScoreInfoChangeDetail]  9 @时间类型 CHAR(1), 10 @开始时间 CHAR(10), 11 @结束时间 CHAR(10), 12 @平台名称 VARCHAR(20), 13 @游戏名称 VARCHAR(20), 14 @UserID    VARCHAR(500) 15 AS 16 BEGIN 17      18  19     SET NOCOUNT ON; 20  21     DECLARE @SQL VARCHAR(MAX),@Joi VARCHAR(500)='',@Par VARCHAR(250); 22  23     SET @Par=' 24         WHERE '+ 25         CASE @时间类型  26             WHEN 1 THEN 'AAI.RegisterDate BETWEEN '''+@开始时间+''' AND '''+@结束时间+'''' -- 注册时间 27             WHEN 2 THEN 'AAI.LastLogonDate BETWEEN '''+@开始时间+''' AND '''+@结束时间+'''' -- 登陆时间 28             WHEN 3 THEN 'TGSIC.ChangeTime BETWEEN '''+@开始时间+''' AND '''+@结束时间+'''' -- 改变时间 29  30     END 31      32     IF(@平台名称 <> '-999') 33     SET @Joi+=' 34         JOIN [dbo].Fn_ParsingMorePar('''+@平台名称+''') AS PLAT ON PLAT.Code <> ''-999'' AND PLAT.Code = AAI.GamePlatId' 35  36     IF(@UserID <> '全部') 37     SET @Joi+=' 38         JOIN [dbo].Fn_ParsingMorePar('''+@UserID+''') AS UserID ON UserID.Code <> ''全部'' AND UserID.Code = AAI.UserID' 39      40     IF(@游戏名称 = 'CatchFish') 41     BEGIN 42     SET @SQL=' 43         SELECT ''新四海龙王'' AS 游戏名称 44             ,AAI.UserID AS 用户ID 45             ,AAI.GameID AS 游戏ID 46             ,AAI.[NickName] AS 用户昵称 47             ,TGSIC.[ChangeColumn] AS 改变字段 48             ,TGSIC.[ChangeTime] AS 改变时间 49             ,TGSIC.[OldColumn] AS 原来值 50             ,TGSIC.[NewColumn] AS 后来值 51             ,CASE  52                 WHEN TGSIC.[OldColumn] > TGSIC.[NewColumn]  53                 THEN ''减少'' 54                 ELSE ''增加'' 55              END AS 类型 56             ,ABS(TGSIC.[OldColumn] - TGSIC.[NewColumn]) AS 变动值 57             ,ISNULL(tE.[EventName],TGSIC.[EventInfo]) AS 活动内容 58             ,ISNULL(GRI.ServerName,'''') AS 游戏场合 59         FROM [CatchFish].[dbo].[QT_GameScoreInfoChange] TGSIC WITH(NOLOCK) 60         JOIN [CatchFish].[dbo].[QA_AccountsInfo] AAI WITH(NOLOCK)ON AAI.UserID = TGSIC.ChangeUserID  61         LEFT JOIN [tbEvent] tE WITH(NOLOCK)ON tE.EventInfo = TGSIC.EventInfo AND tE.Project = ''CatchFish'' 62         OUTER APPLY 63             ( 64                 SELECT TOP 1 TGSLL.ServerID,TGSLL.KindID 65                 FROM [CatchFish].[dbo].QT_GameScoreLockerLog TGSLL 66                 WHERE TGSLL.UserID = TGSIC.ChangeUserID AND TGSLL.CollectDate <= TGSIC.ChangeTime 67                 AND tE.EventName = ''游戏写分'' 68                 ORDER BY TGSLL.CollectDate DESC 69             )ApDraw  70         LEFT JOIN [tbGameRoomInfo] GRI WITH(NOLOCK)ON GRI.ServerID = ApDraw.ServerID AND GRI.KindID = ApDraw.KindID 71  72         '+@Joi+@Par+' 73         ORDER BY 游戏名称,改变时间' 74     END 75  76     IF(@游戏名称 = 'CowCow') 77     BEGIN 78     SET @SQL=' 79         SELECT ''疯狂牛牛'' AS 游戏名称 80             ,AAI.UserID AS 用户ID 81             ,AAI.GameID AS 游戏ID 82             ,AAI.[NickName] AS 用户昵称 83             ,TGSIC.[ChangeColumn] AS 改变字段 84             ,TGSIC.[ChangeTime] AS 改变时间 85             ,TGSIC.[OldColumn] AS 原来值 86             ,TGSIC.[NewColumn] AS 后来值 87             ,CASE  88                 WHEN TGSIC.[OldColumn] > TGSIC.[NewColumn]  89                 THEN ''减少'' 90                 ELSE ''增加'' 91              END AS 类型  92             ,ABS(TGSIC.[OldColumn] - TGSIC.[NewColumn]) AS 变动值 93             ,ISNULL(tE.[EventName],TGSIC.[EventInfo]) AS 活动内容 94             ,ISNULL(GRI.ServerName,'''') AS 游戏场合 95         FROM [CowCow].[dbo].[QT_GameScoreInfoChange] TGSIC WITH(NOLOCK) 96         JOIN [CowCow].[dbo].[QA_AccountsInfo] AAI WITH(NOLOCK)ON AAI.UserID = TGSIC.ChangeUserID  97         LEFT JOIN [tbEvent] tE WITH(NOLOCK)ON tE.EventInfo = TGSIC.EventInfo AND tE.Project = ''CowCow'' 98         OUTER APPLY 99             (100                 SELECT TOP 1 TGSLL.ServerID,TGSLL.KindID101                 FROM [CowCow].[dbo].QT_GameScoreLockerLog TGSLL102                 WHERE TGSLL.UserID = TGSIC.ChangeUserID AND TGSLL.CollectDate <= TGSIC.ChangeTime103                 AND tE.EventName = ''游戏写分''104                 ORDER BY TGSLL.CollectDate DESC105             )ApDraw 106         LEFT JOIN [tbGameRoomInfo] GRI WITH(NOLOCK)ON GRI.ServerID = ApDraw.ServerID AND GRI.GameID = ApDraw.KindID107 108         '+@Joi+@Par+'109         ORDER BY 游戏名称,改变时间'110     END111 112     --PRINT @SQL113     EXEC(@SQL)114 115 END