关于报表开发代码示例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.KindID 101 FROM [CowCow].[dbo].QT_GameScoreLockerLog TGSLL 102 WHERE TGSLL.UserID = TGSIC.ChangeUserID AND TGSLL.CollectDate <= TGSIC.ChangeTime 103 AND tE.EventName = ''游戏写分'' 104 ORDER BY TGSLL.CollectDate DESC 105 )ApDraw 106 LEFT JOIN [tbGameRoomInfo] GRI WITH(NOLOCK)ON GRI.ServerID = ApDraw.ServerID AND GRI.GameID = ApDraw.KindID 107 108 '+@Joi+@Par+' 109 ORDER BY 游戏名称,改变时间' 110 END 111 112 --PRINT @SQL 113 EXEC(@SQL) 114 115 END