22,199
社区成员
发帖
与我相关
我的任务
分享
--1. 每个有 InsertTime 的表建立索引:
CREATE INDEX ix_RecordDrawInfo_InsertTime ON [dbo].RecordDrawInfo(InsertTime)
CREATE INDEX ix_RecordInsure_InsertTime ON [dbo].RecordInsure(InsertTime)
CREATE INDEX ix_RecordGoldBackSum_InsertTime ON [dbo].RecordGoldBackSum(InsertTime)
--2. 连接字段建立索引,这个你自己去完成。列出来的字段你可以 INCLUDE .
--3. 查询把 where 放在每个子项里,提前过滤,不要放外面。
--注意:要用 DATEADD 不要用 DATEDIFF, 比较时左边保持字段纯净无任何包装!
--4. 其它的你自己根据需要可以加或者弄成复合索引
SELECT row_number() OVER (ORDER BY InsertTime DESC) ID, *
FROM (
SELECT DrawID, KindID, ServerID, TableID, UserCount, AndroidCount, Waste, Revenue, UserMedal, StartTime, ConcludeTime, InsertTime, KindName, ServerName,
Score, BackScore, BackInsureScore, gameid, accounts, nickname, lastlogonip, LastLogonType, userID
FROM (SELECT A.DrawID, A.KindID, A.ServerID, A.TableID, A.UserCount, A.AndroidCount, A.Waste, A.Revenue, A.UserMedal, A.StartTime, A.ConcludeTime,
A.InsertTime, B.KindName, C.ServerName, D .Score AS Score, d .BackScore AS BackScore, D .BackInsureScore, E.gameid, E.accounts,
E.nickname, E.lastlogonip, E.LastLogonType, E.userID
FROM [QPTreasureDB].[dbo].RecordDrawInfo AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID JOIN
QPTreasureDB.dbo.RecordDrawScore AS D ON A.DrawID = D .DrawID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON D .UserID = E.userID) AS tab
WHERE Score > 0 AND InsertTime>= DateADD(dd, -7, getdate()) --Score!=0 如果没有负数改成 >0
UNION ALL
SELECT a.[RecordID] AS DrawID, a.[KindID], a.[ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, a.[Revenue], a.[TradeType] AS UserMedal,
a.[CollectDate] AS StartTime, a.[CollectDate] AS ConcludeTime, a.[CollectDate] AS InsertTime,
CASE a.[TradeType] WHEN 1 THEN '存款:' + CAST(a.swapscore AS nvarchar(200)) WHEN 2 THEN '取款:' + CAST(a.swapscore AS nvarchar(200))
WHEN 4 THEN '充值金币:' + CAST(a.swapscore AS nvarchar(200)) WHEN 6 THEN '邮件领取返利:' + CAST(a.swapscore AS nvarchar(200))
ELSE '转账:' + '收款人昵称:' + cast(e.NickName AS nvarchar(200)) + ';收款人游戏ID:' + cast(e.gameid AS nvarchar(200))
+ ';转账金额:' + CAST(a.swapscore AS nvarchar(200)) END AS KindName, '''' AS ServerName, a.swapscore AS Score,
CASE a.[TradeType] WHEN 1 THEN (a.[SourceGold] - a.swapscore) WHEN 2 THEN (a.[SourceGold] + a.swapscore) ELSE a.[SourceGold] END AS BackScore,
CASE WHEN (a.[TradeType] = 1 OR
a.[TradeType] = 4 OR
a.TradeType = 6)
THEN a.SourceBank + a.swapscore WHEN a.[TradeType] = 2 THEN a.SourceBank - a.swapscore WHEN a.[TradeType] = 3 THEN a.SourceBank - a.swapscore
ELSE a.SourceBank END AS BackInsureScore, D .GameID AS gameid, D .Accounts AS accounts, D .NickName AS nockname, a.[ClientIP] AS lastloginip,
d .LastLogonType AS LastLogonType, a.[SourceUserID] AS userID
FROM QPTreasureDB.dbo.RecordInsure AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS D ON A.SourceUserID = D .UserID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON A.TargetUserID = E.UserID
UNION ALL
SELECT a.[RecordID] AS DrawID, a.[KindID], a.[ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, a.[Revenue], 4 AS UserMedal,
a.[CollectDate] AS StartTime, a.[CollectDate] AS ConcludeTime, a.[CollectDate] AS InsertTime, '转入:' + '转出人昵称:' + cast(D .NickName AS nvarchar(200))
+ ';转出游戏ID:' + cast(D .gameid AS nvarchar(200)) + ';转入金额:' + CAST(a.swapscore AS nvarchar(200)) AS KindName, '''' AS ServerName,
a.SwapScore AS Score, a.TargetGold AS BackScore, a.TargetBank + a.swapscore AS BackInsureScore, E.GameID AS gameid, E.Accounts AS accounts,
E.NickName AS nockname, a.[ClientIP] AS lastloginip, E.LastLogonType AS LastLogonType, a.TargetUserID AS userID
FROM QPTreasureDB.dbo.RecordInsure AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS D ON A.SourceUserID = D .UserID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON A.TargetUserID = E.UserID
WHERE a.TradeType = 3 AND InsertTime>= DateADD(dd, -7, getdate())
UNION ALL
SELECT A.RecordID AS DrawID, 0 AS [KindID], 0 AS [ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, 0 AS [Revenue], 5 AS UserMedal,
A.CollectDate AS CollectDate, A.CollectDate AS ConcludeTime, A.CollectDate AS InsertTime, '提取返点:' + CAST(a.BackGold AS nvarchar(200))
AS KindName, '''' AS ServerName, a.BackGold AS Score, a.SourceScore AS BackScore, a.SourceInsureScore AS BackInsureScore, b.gameid AS GameID,
b.Accounts AS accounts, b.NickName AS nockname, a.ClientIP AS lastloginip, b.LastLogonType AS LastLogonType, b.userid AS userID
FROM dbo.RecordGoldBackSum AS A LEFT JOIN
QPAccountsDB.DBO.AccountsInfo AS B ON A.SourceGameID = B.GameID
WHERE a.IsTake = 1 AND InsertTime>= DateADD(dd, -7, getdate())
) AS tab
SELECT *
FROM (SELECT row_number() OVER (ORDER BY InsertTime DESC) ID, *
FROM (SELECT DrawID, KindID, ServerID, TableID, UserCount, AndroidCount, Waste, Revenue, UserMedal, StartTime, ConcludeTime, InsertTime, KindName, ServerName,
Score, BackScore, BackInsureScore, gameid, accounts, nickname, lastlogonip, LastLogonType, userID
FROM (SELECT A.DrawID, A.KindID, A.ServerID, A.TableID, A.UserCount, A.AndroidCount, A.Waste, A.Revenue, A.UserMedal, A.StartTime, A.ConcludeTime,
A.InsertTime, B.KindName, C.ServerName, D .Score AS Score, d .BackScore AS BackScore, D .BackInsureScore, E.gameid, E.accounts,
E.nickname, E.lastlogonip, E.LastLogonType, E.userID
FROM [QPTreasureDB].[dbo].RecordDrawInfo AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID JOIN
QPTreasureDB.dbo.RecordDrawScore AS D ON A.DrawID = D .DrawID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON D .UserID = E.userID) AS tab
WHERE Score != 0
UNION ALL
SELECT a.[RecordID] AS DrawID, a.[KindID], a.[ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, a.[Revenue], a.[TradeType] AS UserMedal,
a.[CollectDate] AS StartTime, a.[CollectDate] AS ConcludeTime, a.[CollectDate] AS InsertTime,
CASE a.[TradeType] WHEN 1 THEN '存款:' + CAST(a.swapscore AS nvarchar(200)) WHEN 2 THEN '取款:' + CAST(a.swapscore AS nvarchar(200))
WHEN 4 THEN '充值金币:' + CAST(a.swapscore AS nvarchar(200)) WHEN 6 THEN '邮件领取返利:' + CAST(a.swapscore AS nvarchar(200))
ELSE '转账:' + '收款人昵称:' + cast(e.NickName AS nvarchar(200)) + ';收款人游戏ID:' + cast(e.gameid AS nvarchar(200))
+ ';转账金额:' + CAST(a.swapscore AS nvarchar(200)) END AS KindName, '''' AS ServerName, a.swapscore AS Score,
CASE a.[TradeType] WHEN 1 THEN (a.[SourceGold] - a.swapscore) WHEN 2 THEN (a.[SourceGold] + a.swapscore) ELSE a.[SourceGold] END AS BackScore,
CASE WHEN (a.[TradeType] = 1 OR
a.[TradeType] = 4 OR
a.TradeType = 6)
THEN a.SourceBank + a.swapscore WHEN a.[TradeType] = 2 THEN a.SourceBank - a.swapscore WHEN a.[TradeType] = 3 THEN a.SourceBank - a.swapscore
ELSE a.SourceBank END AS BackInsureScore, D .GameID AS gameid, D .Accounts AS accounts, D .NickName AS nockname, a.[ClientIP] AS lastloginip,
d .LastLogonType AS LastLogonType, a.[SourceUserID] AS userID
FROM QPTreasureDB.dbo.RecordInsure AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS D ON A.SourceUserID = D .UserID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON A.TargetUserID = E.UserID
UNION ALL
SELECT a.[RecordID] AS DrawID, a.[KindID], a.[ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, a.[Revenue], 4 AS UserMedal,
a.[CollectDate] AS StartTime, a.[CollectDate] AS ConcludeTime, a.[CollectDate] AS InsertTime, '转入:' + '转出人昵称:' + cast(D .NickName AS nvarchar(200))
+ ';转出游戏ID:' + cast(D .gameid AS nvarchar(200)) + ';转入金额:' + CAST(a.swapscore AS nvarchar(200)) AS KindName, '''' AS ServerName,
a.SwapScore AS Score, a.TargetGold AS BackScore, a.TargetBank + a.swapscore AS BackInsureScore, E.GameID AS gameid, E.Accounts AS accounts,
E.NickName AS nockname, a.[ClientIP] AS lastloginip, E.LastLogonType AS LastLogonType, a.TargetUserID AS userID
FROM QPTreasureDB.dbo.RecordInsure AS A LEFT JOIN
QPPlatformDB.dbo.GameKindItem AS B ON A.KindID = B.KindID LEFT JOIN
QPPlatformDB.dbo.GameRoomInfo AS C ON A.ServerID = C.ServerID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS D ON A.SourceUserID = D .UserID LEFT JOIN
QPAccountsDB.dbo.AccountsInfo AS E ON A.TargetUserID = E.UserID
WHERE a.TradeType = 3
UNION ALL
SELECT A.RecordID AS DrawID, 0 AS [KindID], 0 AS [ServerID], 0 AS TableID, 1 AS UserCount, 0 AS AndroidCount, 0 AS Waste, 0 AS [Revenue], 5 AS UserMedal,
A.CollectDate AS CollectDate, A.CollectDate AS ConcludeTime, A.CollectDate AS InsertTime, '提取返点:' + CAST(a.BackGold AS nvarchar(200))
AS KindName, '''' AS ServerName, a.BackGold AS Score, a.SourceScore AS BackScore, a.SourceInsureScore AS BackInsureScore, b.gameid AS GameID,
b.Accounts AS accounts, b.NickName AS nockname, a.ClientIP AS lastloginip, b.LastLogonType AS LastLogonType, b.userid AS userID
FROM dbo.RecordGoldBackSum AS A LEFT JOIN
QPAccountsDB.DBO.AccountsInfo AS B ON A.SourceGameID = B.GameID
WHERE a.IsTake = 1) AS tab
WHERE DateDiff(dd, InsertTime, getdate()) <= 7) AS two