程序员小小黑 2019-06-13 23:27 采纳率: 0%
浏览 735

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条回答 默认 最新

  • 德玛洗牙 2019-06-14 15:34
    关注

    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

    评论

报告相同问题?

悬赏问题

  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 ubuntu系统下挂载磁盘上执行./提示权限不够
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码