SELECT u.UserID as '玩家Id',u.AreaID as '渠道ID',o.AreaName as '渠道名称', u.LoginName as '手机号',m.Amount/100 as '玩家身上金币' ,n.Amount/100 as '银行金币',u.RealName as '支付宝实名', u.AlipayAccount as '支付宝账号',u.BankAccountName as '银行卡实名',u.BankAccountNumber as '银行卡账号', (u.SumCharge+u.SumAgentCharge)/100 as '总充值' ,u.SumConvert/100 as '总兑换',u.CreateTime as '注册时间',u.LastLogonTime as '最后登录时间' FROM (((CenterDB.Users.UserInfo u WITH ( nolock ) LEFT JOIN CenterDB.Money.Cash m WITH(nolock) ON u.UserID = m.UserID ) LEFT JOIN CenterDB.Money.Bank n WITH(nolock) ON u.UserID = n.UserID) LEFT JOIN CenterDB.Area.Area o WITH(nolock) ON o.AreaID = u.AreaID ) WHERE u.GroupID = 70001 --渠道Id自行修改 AND (u.SumCharge + u.SumAgentCharge) = 0 -->0 充值 =0 未充值 AND u.UserType=1 -- 正式玩家 SELECT u.UserID as '玩家Id',u.AreaID as '渠道ID',o.AreaName as '渠道名称', u.LoginName as '手机号',u.RealName as '支付宝实名', u.AlipayAccount as '支付宝账号',u.BankAccountName as '银行卡实名',u.BankAccountNumber as '银行卡账号', (u.SumCharge+u.SumAgentCharge)/100 as '总充值' ,u.SumConvert/100 as '总兑换',u.CreateTime as '注册时间',u.LastLogonTime as '最后登录时间' FROM CenterDB.Users.UserInfo u WITH ( nolock ) LEFT JOIN CenterDB.Area.Area o WITH(nolock) ON o.AreaID = u.AreaID WHERE u.GroupID = 70001 --渠道Id自行修改 AND (u.SumCharge + u.SumAgentCharge) = 0 -->0 充值 =0 未充值 AND u.UserType=1 -- 正式玩家 SELECT u.UserID AS '玩家Id', u.AreaID AS '渠道ID', o.AreaName AS '渠道名称', u.LoginName AS '手机号', ( u.SumCharge+ u.SumAgentCharge ) / 100 AS '总充值', u.SumConvert/ 100 AS '总兑换', u.CreateTime AS '注册时间', u.LastLogonTime AS '最后登录时间' FROM CenterDB.Users.UserInfo u WITH ( nolock ) LEFT JOIN CenterDB.Area.Area o WITH ( nolock ) ON o.AreaID = u.AreaID WHERE u.GroupID = 70001 --包网Id自己改 AND ( u.SumCharge + u.SumAgentCharge ) > 0 -->0 充值 =0 未充值 AND u.UserType= 1 -- 正式玩家 SELECT u.UserID as '玩家Id',u.AreaID as '渠道ID',o.AreaName as '渠道名称', u.LoginName as '手机号',(u.SumCharge+u.SumAgentCharge)/100 as '充值' ,u.SumConvert/100 as '总兑换',m.Amount as '身上剩余金币',n.Amount as '保险箱剩余金币',u.LastLogonTime as '最后登录时间' FROM ((CenterDB.Users.UserInfo u WITH ( nolock ) LEFT JOIN CenterDB.Money.Cash m WITH(nolock) ON u.UserID = m.UserID) LEFT JOIN CenterDB.Money.Bank n WITH(nolock) ON u.UserID = n.UserID) LEFT JOIN CenterDB.Area.Area o WITH(nolock) ON o.AreaID = u.AreaID WHERE u.GroupID = 70006 --大圣包网 AND LEFT ( u.AreaID, 5 ) ='10244' -- 999大玩家 AND (u.SumCharge + u.SumAgentCharge) > 0 --有充值的玩家(可自行修改限制金额单位分) AND u.UserType=1 -- 正式玩家 999-大圣-大玩家 SELECT u.UserID AS '玩家Id', u.AreaID AS '渠道ID', o.AreaName AS '渠道名称', u.LoginName AS '手机号', ( u.SumCharge+ u.SumAgentCharge ) / 100 AS '总充值', u.SumConvert/ 100 AS '总兑换', u.CreateTime AS '注册时间', u.LastLogonTime AS '最后登录时间' FROM CenterDB.Users.UserInfo u WITH ( nolock ) LEFT JOIN CenterDB.Area.Area o WITH ( nolock ) ON o.AreaID = u.AreaID WHERE u.GroupID = 70001 --包网Id自己改 AND ( u.SumCharge + u.SumAgentCharge ) > 0 -->0 充值 =0 未充值 AND u.UserType= 1 -- 正式玩家 SELECT u.UserID AS '玩家Id', u.AreaID AS '渠道ID', o.AreaName AS '渠道名称', u.LoginName AS '手机号', ( u.SumCharge+ u.SumAgentCharge ) / 100 AS '总充值', u.SumConvert/ 100 AS '总兑换', u.CreateTime AS '注册时间', u.LastLogonTime AS '最后登录时间' FROM CenterDB.Users.UserInfo u WITH ( nolock ) LEFT JOIN CenterDB.Area.Area o WITH ( nolock ) ON o.AreaID = u.AreaID WHERE ( u.SumCharge + u.SumAgentCharge ) > 0 AND u.UserType= 0 SELECT TOP 1 u.UserID AS '玩家Id', u.AreaID AS '渠道ID', o.AreaName AS '渠道名称', u.LoginName AS '手机号', ( u.SumCharge+ u.SumAgentCharge ) / 100 AS '总充值', u.SumConvert/ 100 AS '总兑换', u.CreateTime AS '注册时间', u.LastLogonTime AS '最后登录时间' FROM CenterDB.Users.UserInfo u WITH ( nolock ) LEFT JOIN CenterDB.Area.Area o WITH ( nolock ) ON o.AreaID = u.AreaID WHERE u.UserType= 1 AND ( u.SumCharge + u.SumAgentCharge ) > 0 -->0 充值 =0 未充值 AND u.UserID NOT IN ( SELECT TOP 10 --自己改偏移量 u.UserID FROM ##### 查询 777 充值大于零的所有玩家 ##### SELECT u.UserID AS '玩家Id', u.AreaID AS '渠道ID', o.AreaName AS '渠道名称', u.LoginName AS '手机号', ( u.SumCharge+ u.SumAgentCharge ) / 100 AS '总充值', u.SumConvert/ 100 AS '总兑换', u.CreateTime AS '注册时间', u.LastLogonTime AS '最后登录时间' FROM CenterDB.Users.UserInfo u WITH ( nolock ) LEFT JOIN CenterDB.Area.Area o WITH ( nolock ) ON o.AreaID = u.AreaID WHERE ( u.SumCharge + u.SumAgentCharge ) > 0 -->0 充值 =0 未充值 AND u.UserType= 1 -- 正式玩家 ##### 查询777在某一段时间登录过的玩家 ### SELECT u.UserID AS '玩家Id', u.AreaID AS '渠道ID', o.AreaName AS '渠道名称', u.LoginName AS '手机号', ( u.SumCharge+ u.SumAgentCharge ) / 100 AS '总充值', u.SumConvert/ 100 AS '总兑换', u.CreateTime AS '注册时间', u.LastLogonTime AS '最后登录时间' FROM CenterDB.Users.UserInfo u WITH ( nolock ) LEFT JOIN CenterDB.Area.Area o WITH ( nolock ) ON o.AreaID = u.AreaID WHERE ( u.SumCharge + u.SumAgentCharge ) > 0 -->0 充值 =0 未充值 AND u.UserType= 1 -- 正式玩家 AND u.LastLogonTime > '2019-01-01' --在2019-01-01之后登录过 AND u.LastLogonTime < '2021-04-25' --在2020-04-25之前登录过 #999 自营 包网 麦发包网 #所有玩过视讯类游戏玩家数据拉出来 #只拉一个月以上没登录过的 SELECT u.UserID AS '玩家Id', u.AreaID AS '渠道ID', o.AreaName AS '渠道名称', u.LoginName AS '手机号', ( u.SumCharge+ u.SumAgentCharge ) / 100 AS '总充值', u.SumConvert/ 100 AS '总兑换', u.CreateTime AS '注册时间', u.LastLogonTime AS '最后登录时间' FROM CenterDB.Users.UserInfo u WITH ( nolock ) LEFT JOIN CenterDB.Area.Area o WITH ( nolock ) ON o.AreaID = u.AreaID WHERE ( u.SumCharge + u.SumAgentCharge ) > 0 -->0 充值 =0 未充值 AND u.UserType= 1 -- 正式玩家 AND u.LastLogonTime < '2021-05-07 00:00:00' AND UserID IN (SELECT DISTINCT UserID FROM PerGameLog.PerLog.RoomTotalLoseWinLog WHERE ServerID IN ( 3301, 2901, 3201, 3401, 7201, 7301, 7401, 7501, 7601, 7901 ) AND ABS(UpdateAmount) > 5000) ### 查询999自营70001 2021年所有有充值的玩家### SELECT u.UserID AS '玩家Id', u.AreaID AS '渠道ID', o.AreaName AS '渠道名称', u.LoginName AS '手机号', ( u.SumCharge+ u.SumAgentCharge ) / 100 AS '总充值', u.SumConvert/ 100 AS '总兑换', u.CreateTime AS '注册时间', u.LastLogonTime AS '最后登录时间' FROM CenterDB.Users.UserInfo u WITH ( nolock ) LEFT JOIN CenterDB.Area.Area o WITH ( nolock ) ON o.AreaID = u.AreaID WHERE u.GroupID = 70001 --包网Id自己改 AND ( u.SumCharge + u.SumAgentCharge ) > 0 -->0 充值 =0 未充值 AND u.UserType= 1 -- 正式玩家 AND u.LastLogonTime > '2019-01-01' --在2019-01-01之后登录过 AND u.LastLogonTime < '2021-04-25' --在2020-04-25之前登录过 ##### 拉取999 麦发 所有玩家数据 ###### SELECT u.UserID as '玩家Id',u.AreaID as '渠道ID',o.AreaName as '渠道名称', u.LoginName as '手机号',u.RealName as '支付宝实名', u.AlipayAccount as '支付宝账号',u.BankAccountName as '银行卡实名',u.BankAccountNumber as '银行卡账号', (u.SumCharge+u.SumAgentCharge)/100 as '总充值' ,u.SumConvert/100 as '总兑换',u.CreateTime as '注册时间',u.LastLogonTime as '最后登录时间' FROM CenterDB.Users.UserInfo u WITH ( nolock ) LEFT JOIN CenterDB.Area.Area o WITH(nolock) ON o.AreaID = u.AreaID WHERE (u.SumCharge + u.SumAgentCharge) = 0 -->0 充值 =0 未充值 AND u.UserType=1 -- 正式玩家