查询Lobby Server卡死后对于CashOut的影响的DB脚本

 Lobby Server因为WS没有返回的问题，上个周末(11月5号和6号) Lobby Server卡住了两次。虽然后来通过重启解决了问题，但是不可避免的结果是，Cashout的值会少记不少。

我已经让Bob同学查了一下日志，他大概给了一下出事的时间里在出事的房间里的人数：

room:16  (2011-11-05 18:58:02重启)
2011-11-05 18:26:24 10 player in lobby server

room:11 (2011-11-05 15:32:06、2011-11-05 18:18:02重启)
2011-11-05 13:49:37 18 player in lobby server 
2011-11-05 17:24:33 26 player in lobby server

 

但是应该有准确的统计方法，先来看用到的CrossTransactionHistory的表的结构：

 

用下面的SQL来寻找在一定时间范围内带入次数比带出次数多的玩家信息：

SELECT TIn.UserName, TIn.InTime, TOut.OutTime, TIn.SumMoney AS InMoney, TOut.SumMoney AS OutMoney, 
	TIn.SumMoney AS SumIn,TOut.SumMoney AS SumOut, TIn.FirstInTime, TOut.FirstOutTime, TIn.LastInTime, TOut.LastOutTime
FROM
	(SELECT UserName, COUNT(TransactionType) AS InTime, sum(Money) as SumMoney, min(TransactionTime) AS FirstInTime,
		max(TransactionTime) as LastInTime
		FROM CrossTransactionHistory_TEMP_1
		WHERE RoomID = 11
			AND TransactionTime BETWEEN DATE('2011-11-05 15:00:00') AND DATE('2011-11-07 12:00:00')
			AND TransactionType = 0
			AND ErrorCode = 0
			GROUP BY UserName
			ORDER BY UserName DESC) AS TIn
LEFT OUTER JOIN 
	(SELECT UserName, COUNT(TransactionType) AS OutTime, sum(Money) as SumMoney, min(TransactionTime) AS FirstOutTime,
		max(TransactionTime) as LastOutTime
		FROM CrossTransactionHistory_TEMP_1
		WHERE RoomID = 11
			AND TransactionTime BETWEEN DATE('2011-11-05 15:00:00') AND DATE('2011-11-07 12:00:00')
			AND TransactionType = 1
			AND ErrorCode = 0
			GROUP BY UserName
			ORDER BY UserName DESC) AS TOut
	ON TIn.UserName = TOut.UserName
WHERE TIn.InTime > TOut.OutTime
ORDER BY TIn.UserName;

再用下面的存储过程用来检查这个玩家是不是真的出现了连续两次带钱进桌的情况：

DROP PROCEDURE IF EXISTS CheckCashOut;
CREATE PROCEDURE CheckCashOut(IN UName varchar(64),IN RID int(10))
	BEGIN
		DECLARE RCount int;
		DROP TABLE IF EXISTS CheckCashOut_Temp;
		/*添加一个临时表*/
		CREATE TABLE CheckCashOut_Temp (
			ID bigint(20) NOT NULL AUTO_INCREMENT,
			UserName varchar(64) NOT NULL,
			RoomID int(10) NOT NULL,
			TransactionType int(11) NOT NULL,
			TransactionTime datetime NOT NULL,
			PRIMARY KEY (ID)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		/*向临时表中插入按需要排列的数据列*/
		INSERT INTO CheckCashOut_Temp (UserName, RoomID, TransactionType,TransactionTime)
			SELECT  UserName, RoomID, TransactionType, TransactionTime
			FROM CrossTransactionHistory_TEMP_1 
			WHERE TransactionType IN (0,1)
				AND UserName = UName
				AND RoomID = RID
			ORDER BY TransactionTime;
		/*检查相邻的两列中的TransactionType是否是一样的*/
		/*即是否有连续两次的带入记录*/
		SELECT A.ID, B.ID, A.TransactionType, B.TransactionType
		FROM CheckCashOut_Temp A, CheckCashOut_Temp B
		WHERE ABS(A.ID - B.ID)=1
			AND A.ID <= B.ID
			AND A.TransactionType = 0
			AND A.TransactionType = B.TransactionType;
		SELECT FOUND_ROWS() INTO RCount;
		SELECT RCount;
		DROP TABLE IF EXISTS CheckCashOut_Temp;
	END;

一个用户一个用户地检查，如果真的出现了就算是一个。这样得出的人数应该是比较准确的。