mysql 多个join遇到笛卡尔积怎么办？
一、需求
根据用户在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
select t1.qq,t1.nickname,t2.challenge_result,t2.kda,t2.create_time,t3.challenge_result,t3.kda,t3.create_time from lol_user t1
left join
( select t1.user_id,t2.challenge_result,t2.kda,t2.create_time from lol_level_challenge_history t1
left join
lol_level_challenge_history_detail t2
on t1.id=t2.id
where
level_type='1'
) t2
on
t1.id=t2.user_id
left join
(select t1.user_id,t2.challenge_result,t2.kda,t2.create_time from lol_level_challenge_history t1
left join
lol_level_challenge_history_detail t2
on t1.id=t2.id
where
level_type='2'
) t3
on
t1.id=t3.user_id
换了点思路后，得出了想要的结果
SELECT
t2.id,
t2.nickname as '昵称',
(case when max( t2.challenge_result1 ) = 1 then '成功'
when max( t2.challenge_result1 ) = 2 then '失败'
else null end) as '比赛结果1',
max( t2.kda1 ) AS kda1,
max( t2.create_time1 ) AS '比赛时间1',
(case when max( t2.challenge_result2 ) = 1 then '成功'
when max( t2.challenge_result2 ) = 2 then '失败'
else null end) as '比赛结果2',
max( t2.kda2 ) AS kda2,
max( t2.create_time2 ) AS '比赛时间2'
FROM
(
SELECT
t1.*,
@groupRow :=
IF
( @historyId = t1.level_challenge_history_id, @groupRow + 1, 1 ) AS groupRow,
@historyId := t1.level_challenge_history_id
FROM
(
SELECT
u.id,
u.nickname,
h.id AS level_challenge_history_id,
( CASE WHEN level_type = '1' THEN d.challenge_result END ) AS challenge_result1,
( CASE WHEN level_type = '1' THEN d.kda END ) kda1,
( CASE WHEN level_type = '1' THEN d.create_time END ) create_time1,
( CASE WHEN level_type = '2' THEN d.challenge_result END ) AS challenge_result2,
( CASE WHEN level_type = '2' THEN d.kda END ) kda2,
( CASE WHEN level_type = '2' THEN d.create_time END ) create_time2
FROM
lol_level_challenge_history h
LEFT JOIN lol_level_challenge_history_detail d ON ( h.id = d.level_challenge_history_id )
LEFT JOIN lol_user u ON ( h.user_id = u.id )
WHERE
h.id IN ( SELECT max( id ) FROM lol_level_challenge_history GROUP BY user_id, level_type )
ORDER BY
u.id,
h.level_type,
d.create_time
) t1
) t2
GROUP BY
t2.id,
t2.groupRow