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

2个回答

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

in_the_road
委屈的tony 重新想了下 这条能查出来 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
7 个月之前 回复
qq_36720114
德玛洗牙 回复委屈的tony: 我看了一下你好像也没有改对,是t1.level_type=t2.level_challenge_history_id吧
7 个月之前 回复
in_the_road
委屈的tony 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'这句中的 left join的表字段错了,应该是t1.id = t2.level_challenge_history_id ,改正之后就会出现笛卡儿积情况
7 个月之前 回复

换了点思路后,得出了想要的结果

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
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问