一、需求
根据用户在LOL中打晋级赛的结果记录,统计出如下表结构的数据
规则介绍:
1、晋级赛最多打5把,三局两胜制;
2、提前胜三局,判定晋级成功,无需打满5把
二、表结构
三、表结构SQL
CREATE TABLE `lol_user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`qq` varchar(255) DEFAULT NULL COMMENT 'QQ号',
`nickname` varchar(255) DEFAULT NULL COMMENT '昵称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `lol_level_challenge_history` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`level_type` tinyint(4) DEFAULT NULL COMMENT '段位类型(1:黑铁;2:黄铜;3:白银)',
`user_id` int(11) DEFAULT NULL COMMENT '用户id',
`challenge_result` tinyint(255) DEFAULT NULL COMMENT '挑战结果(1:成功;2:失败)',
`create_time` datetime(3) DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户段位挑战历史';
CREATE TABLE `lol_level_challenge_history_detail` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`level_challenge_history_id` int(11) DEFAULT NULL COMMENT '用户段位挑战历史id',
`challenge_result` tinyint(4) DEFAULT NULL COMMENT '挑战结果(1:成功;2:失败)',
`kda` double(10,2) DEFAULT NULL COMMENT 'KDA分数',
`create_time` datetime(3) DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户段位挑战历史详情';
四、表数据SQL
INSERT INTO `lol_user`(`id`, `qq`, `nickname`) VALUES (1, '6666666', '草帽路飞');
INSERT INTO `lol_user`(`id`, `qq`, `nickname`) VALUES (2, '7777777', '夷陵老祖');
INSERT INTO `lol_level_challenge_history`(`id`, `level_type`, `user_id`, `challenge_result`, `create_time`) VALUES (1, 1, 1, 1, '2019-06-10 22:32:24.000');
INSERT INTO `lol_level_challenge_history`(`id`, `level_type`, `user_id`, `challenge_result`, `create_time`) VALUES (3, 2, 1, 2, '2019-06-13 22:34:46.000');
INSERT INTO `lol_level_challenge_history_detail`(`id`, `level_challenge_history_id`, `challenge_result`, `kda`, `create_time`) VALUES (1, 1, 1, 12.00, '2019-06-10 14:35:33.000');
INSERT INTO `lol_level_challenge_history_detail`(`id`, `level_challenge_history_id`, `challenge_result`, `kda`, `create_time`) VALUES (2, 1, 2, 5.00, '2019-06-10 15:35:49.000');
INSERT INTO `lol_level_challenge_history_detail`(`id`, `level_challenge_history_id`, `challenge_result`, `kda`, `create_time`) VALUES (3, 1, 1, 14.00, '2019-06-10 16:36:28.000');
INSERT INTO `lol_level_challenge_history_detail`(`id`, `level_challenge_history_id`, `challenge_result`, `kda`, `create_time`) VALUES (4, 1, 1, 13.00, '2019-06-10 17:38:28.000');
INSERT INTO `lol_level_challenge_history_detail`(`id`, `level_challenge_history_id`, `challenge_result`, `kda`, `create_time`) VALUES (5, 3, 1, 13.00, '2019-06-13 07:39:31.000');
INSERT INTO `lol_level_challenge_history_detail`(`id`, `level_challenge_history_id`, `challenge_result`, `kda`, `create_time`) VALUES (6, 3, 1, 16.00, '2019-06-13 08:39:52.000');
INSERT INTO `lol_level_challenge_history_detail`(`id`, `level_challenge_history_id`, `challenge_result`, `kda`, `create_time`) VALUES (7, 3, 2, 6.00, '2019-06-13 09:39:52.000');
INSERT INTO `lol_level_challenge_history_detail`(`id`, `level_challenge_history_id`, `challenge_result`, `kda`, `create_time`) VALUES (8, 3, 2, 5.00, '2019-06-13 10:39:52.000');
INSERT INTO `lol_level_challenge_history_detail`(`id`, `level_challenge_history_id`, `challenge_result`, `kda`, `create_time`) VALUES (9, 3, 2, 4.00, '2019-06-13 11:39:52.000');
五、错误的写法
SELECT
u.id,
u.nickname,
u.qq,
d1.challenge_result AS '比赛结果1',
d1.kda AS 'KDA评分1',
d1.create_time AS '比赛时间1',
d2.challenge_result AS '比赛结果2',
d2.kda AS 'KDA评分2',
d2.create_time AS '比赛时间2'
FROM
(
SELECT
max( CASE WHEN level_type = '1' THEN id ELSE 0 END ) AS heitie,
max( CASE WHEN level_type = '2' THEN id ELSE 0 END ) AS baiyin,
user_id
FROM
lol_level_challenge_history
GROUP BY
user_id
) t
LEFT JOIN lol_level_challenge_history_detail d1 ON ( t.heitie = d1.level_challenge_history_id )
LEFT JOIN lol_level_challenge_history_detail d2 ON ( t.baiyin = d2.level_challenge_history_id )
LEFT JOIN lol_user u ON ( t.user_id = u.id )
ORDER BY
u.id