slambb_smartbowgame/SmartBowBusinessServer/res/sql/获取用户游戏记录.sql

SELECT 
	id, nickname, 
	if(gender = 2, '女', '男') as gender, 
	phone, birthday, 
	integral, coin, diamond, mac,
	country, state, city, 
	if (offlineTime > 0, FROM_UNIXTIME(offlineTime / 1000, '%Y-%m-%d %H:%i'), '') as offlineTime, 
	CEIL(totalGameDuration / 60) 'totalGameDuration', totalGameCount, 
	date, 
	CEIL(dateGameDuration / 60) 'dateGameDuration', dateGameCount, 
	CEIL(game1_duration / 60) 'game1_duration', game1_count, 
	CEIL(game2_duration / 60) 'game2_duration', game2_count, 
	CEIL(game3_duration / 60) 'game3_duration', game3_count, 
	time, gameType, CEIL(duration / 60) 'duration'
FROM 

(
	(
		user_info #表1-用户信息
		LEFT JOIN 
		( 
			SELECT userID, sum(duration) as totalGameDuration, count(*) as totalGameCount
			FROM user_game_record
			GROUP BY userID
		) total_game_record #表2-用户总游戏记录
		ON user_info.id = total_game_record.userID
	)
	LEFT JOIN
	(
		SELECT 
			userID, 
			date_format(dateTime, '%Y-%m-%d') as date, 
			sum(duration) as dateGameDuration, count(*) as dateGameCount, 
			sum(if(gameType in (1), duration, 0)) as game1_duration, 
			count(if(gameType in (1), TRUE, null)) as game1_count, 
			sum(if(gameType in (3,4,5), duration, 0)) as game2_duration, 
			count(if(gameType in (3,4,5), TRUE, null)) as game2_count, 
			sum(if(gameType in (2,6,7,8,9,10,11,12), duration, 0)) as game3_duration, 
			count(if(gameType in (2,6,7,8,9,10,11,12), TRUE, null)) as game3_count
		FROM user_game_record
		GROUP BY userID, date_format(dateTime, '%Y-%m-%d')
	) date_game_record #表3-用户某日游戏记录
	ON user_info.id = date_game_record.userID
)
LEFT JOIN
(
	SELECT userID, date_format(dateTime, '%Y-%m-%d') as date0, date_format(dateTime, '%H:%i') as time, gameType, duration
	FROM user_game_record
) time_game_record #表4-用户某日时间游戏记录
ON date_game_record.userID = time_game_record.userID AND date_game_record.date = time_game_record.date0

ORDER BY totalGameDuration DESC, id, date DESC, time