granafa的调试sql语句参考

txidc2024


SELECT SUM(slws.summary_bmandata.gamegold) / 2500 as new_gamegold, slws.summary_businessman.bname FROM slws.summary_bmandata  INNER JOIN slws.summary_businessman ON slws.summary_bmandata.businessman_id = slws.summary_businessman.id  GROUP BY slws.summary_bmandata.businessman_id;
SELECT slws.summary_server.sname as 分区名, slws.summary_seversum.online as 在线人数, slws.summary_seversum.uptime FROM slws.summary_seversum INNER JOIN slws.summary_server on slws.summary_seversum.server_id = slws.summary_server.id where slws.summary_seversum.uptime  > NOW() - INTERVAL 1 HOUR
SELECT slws.summary_server.sname as 分区名, slws.summary_seversum.online as 在线人数, slws.summary_seversum.uptime FROM slws.summary_seversum INNER JOIN slws.summary_server on slws.summary_seversum.server_id = slws.summary_server.id where slws.summary_seversum.uptime  > NOW() - INTERVAL 1 HOUR

SELECT  pname as 玩家名, level as 等级, gamegold as 元宝, gamegird as 充值, consume as 消耗,  income as 产出 FROM slws.summary_playerdata WHERE (wid <> 'owYrM6RJKulPnpegA8L7FWXNz69M' AND wid <> 'owYrM6SZ0npxDtZ0mDYjGZTlUUGs' AND wid <> 'owYrM6T3lzppKtVpeK-qYk4aMdBg' AND wid <> 'owYrM6U98dRkQzSz46tIBwl8UqgU' AND wid <> 'owYrM6XGW64GdXOKNBBZpF8H1flo') ORDER BY gamegold DESC LIMIT 100 

SELECT wxid, num, tgamegold, uptime FROM slws.summary_playsellsum where uptime > NOW() INTEVAL - 72 HOUR ORDER BY num DESC LIMIT 50 

SELECT wxid, num, tgamegold, uptime FROM slws.summary_playsellsum where uptime > NOW() - INTERVAL 72 HOUR ORDER BY num DESC LIMIT 50 
SELECT slws.summary_playsellsum.wxid as 微信, COUNT(slws.summary_playsellinfo.buyprice) AS 次数, SUM(slws.summary_playsellinfo.buyprice) as 总数 FROM slws.summary_playsellinfo  inner join slws.summary_playsellsum on slws.summary_playsellinfo.playsellsum_id = slws.summary_playsellsum.id where slws.summary_playsellinfo.addtime > NOW() - INTERVAL 72 HOUR GROUP BY slws.summary_playsellinfo.playsellsum_id  order by 次数 DESC LIMIT 50 
SELECT slws.summary_playsellsum.wxid as 微信, COUNT(slws.summary_playsellinfo.buyprice) AS 次数, SUM(slws.summary_playsellinfo.buyprice) as 总数 FROM slws.summary_playsellinfo  inner join slws.summary_playsellsum on slws.summary_playsellinfo.playsellsum_id = slws.summary_playsellsum.id where slws.summary_playsellinfo.addtime > NOW() - INTERVAL 72 HOUR GROUP BY slws.summary_playsellinfo.playsellsum_id  order by 次数 DESC, 总数 DESC LIMIT 50 


SELECT slws.summary_playerdata.wid as 微信, COUNT(slws.summary_playtradeinfo.ingamegold) as 次数, SUM(slws.summary_playtradeinfo.ingamegold) as 总数 FROM slws.summary_playtradeinfo inner join slws.summary_playerdata on  slws.summary_playtradeinfo.buyplay_id = slws.summary_playerdata.id  where slws.summary_playtradeinfo.addtime > NOW() - INTERVAL 72 HOUR GROUP BY slws.summary_playerdata.wid  ORDER BY 次数 DESC, 总数 DESC LIMIT 50 

SELECT  count(slws.summary_equip.name), slws.summary_server.sname  FROM slws.query_mondropitems inner join   slws.summary_server on slws.query_mondropitems.server_id =  slws.summary_server.id   inner join  slws.summary_equip on slws.query_mondropitems.equip_id = slws.summary_equip.id  where slws.summary_equip.name LIKE '%火龙%'  and slws.summary_server.sname LIKE '%区%'  GROUP BY slws.summary_server.sname order by slws.summary_server.starttime DESC LIMIT 60;
SELECT SUM(consumegold) FROM slws.summary_seversum LIMIT 50 

SELECT SUM(consumegold) / 2500 AS 昨日产消 FROM slws.summary_seversum WHERE uptime LIMIT '%09:58%' and uptime > NOW() - INTERVAL 24 HOUR

SELECT allgamegold as 总元宝, DATE_SUB(addTime, INTERVAL 8 HOUR) AS new_time, allbgamegold FROM slws.summary_gamecollect ORDER BY id DESC LIMIT 720 


SELECT slws.summary_seversum.dropnum FROM slws.summary_seversum inner join slws.summary_seversum on slws.summary_seversum.server_id = slws.summary_seversum.id WHERE slws.summary_seversum.name like  '%区%'GROUP BY slws.summary_seversum.server_id order by slws.summary_seversum.uptime DESC LIMIT 1; 

SELECT slws.summary_server.sname,r1.dropnum
FROM slws.summary_seversum r1 
JOIN (
    SELECT server_id, MAX(uptime) AS max_timestamp
    FROM slws.summary_seversum
    GROUP BY server_id
) r2 ON r1.server_id = r2.server_id AND r1.uptime = r2.max_timestamp inner Join slws.summary_server on r1.server_id = slws.summary_server.id where slws.summary_server.sname like "%区%"  order by r1.uptime desc LIMIT 40;


SELECT allgamegold as 总元宝, DATE_SUB(addTime, INTERVAL 8 HOUR) AS new_time, allbgamegold FROM slws.summary_gamecollect ORDER BY id DESC LIMIT 720 

SELECT  t2.sname as 分区, t1.pname as 玩家名, t1.level as 等级,  t1.gamegold as 元宝,  t1.gamegird as 充值,  t1.consume as 消耗,   t1.income as 产出 FROM slws.summary_playerdata t1  inner join slws.summary_server t2 on t1.server_id = t2.id WHERE (wid <> 'owYrM6RJKulPnpegA8L7FWXNz69M' AND wid <> 'owYrM6SZ0npxDtZ0mDYjGZTlUUGs' AND wid <> 'owYrM6T3lzppKtVpeK-qYk4aMdBg' AND wid <> 'owYrM6U98dRkQzSz46tIBwl8UqgU' AND wid <> 'owYrM6XGW64GdXOKNBBZpF8H1flo')  ORDER BY gamegold DESC LIMIT 100

txidc2024

SELECT (allgamegold /2500 + 装备价值) AS 总价值, addtime FROM slws.summary_gamecollect T1 
left join
(SELECT Sum(incomegold) / 2500 AS 装备价值, uptime FROM slws.summary_seversum  GROUP BY uptime )  T2
on DATE_FORMAT(T2.uptime, '%Y-%m-%d %H') = DATE_FORMAT(T1.addtime, '%Y-%m-%d %H')

txidc2024

SELECT  count(slws.summary_equip.name), slws.summary_server.sname
FROM slws.query_mondropitems inner join slws.summary_server
on slws.query_mondropitems.server_id =  slws.summary_server.id   
inner join  slws.summary_equip 
on slws.query_mondropitems.equip_id = slws.summary_equip.id  
where slws.summary_equip.name in ("圣龙战刃", "圣龙魔刃", "圣龙道刃", "圣龙神甲", "圣龙神袍")  
GROUP BY slws.summary_server.sname order by slws.summary_server.starttime DESC LIMIT 20;

SELECT count(consumegold) as 全服产消, uptime FROM slws.summary_seversum WHERE uptime like '%09:5%'GROUP by uptime ;

txidc2024

SELECT  slws.summary_equip.name, slws.summary_server.sname
FROM slws.query_mondropitems inner join slws.summary_server
on slws.query_mondropitems.server_id =  slws.summary_server.id   
inner join  slws.summary_equip 
on slws.query_mondropitems.equip_id = slws.summary_equip.id  
where slws.summary_equip.name in ("圣龙道链", "圣龙魔链", "圣龙战链")  
group by slws.summary_server.sname
order by slws.summary_server.starttime DESC LIMIT 20;