MySQL左连接两张表分别count两张表数据时数量变为乘积

作者: 殷俊杰 | 来源:发表于2018-08-31 10:20 被阅读0次

CREATE TABLE `t_video` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `describe` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `t_comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `video_id` int(11) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `video_id` (`video_id`),
  CONSTRAINT `t_comment_ibfk_1` FOREIGN KEY (`video_id`) REFERENCES `t_video` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

CREATE TABLE `t_praise` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `video_id` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `video_id` (`video_id`),
  CONSTRAINT `t_praise_ibfk_1` FOREIGN KEY (`video_id`) REFERENCES `t_video` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

INSERT INTO `test`.`t_video` (`id`, `name`, `describe`) VALUES ('1', '自拍', '自拍');
INSERT INTO `test`.`t_video` (`id`, `name`, `describe`) VALUES ('2', '小猫咪', '小猫咪');
INSERT INTO `test`.`t_video` (`id`, `name`, `describe`) VALUES ('3', '小狗狗', '小狗狗');
INSERT INTO `test`.`t_video` (`id`, `name`, `describe`) VALUES ('4', '驴蛋蛋', '驴蛋蛋');
INSERT INTO `test`.`t_video` (`id`, `name`, `describe`) VALUES ('5', '小脑腐', '小脑腐');

INSERT INTO `test`.`t_comment` (`id`, `video_id`, `comment`) VALUES ('1', '1', '啊好好看');
INSERT INTO `test`.`t_comment` (`id`, `video_id`, `comment`) VALUES ('2', '1', '啊好丑啊');
INSERT INTO `test`.`t_comment` (`id`, `video_id`, `comment`) VALUES ('3', '1', '你是魔鬼吗');
INSERT INTO `test`.`t_comment` (`id`, `video_id`, `comment`) VALUES ('4', '2', '喵喵喵');
INSERT INTO `test`.`t_comment` (`id`, `video_id`, `comment`) VALUES ('5', '2', '喵喵喵');
INSERT INTO `test`.`t_comment` (`id`, `video_id`, `comment`) VALUES ('6', '3', '汪汪汪');

INSERT INTO `test`.`t_praise` (`id`, `video_id`, `create_time`) VALUES ('1', '1', '2018-12-12 16:59:08');
INSERT INTO `test`.`t_praise` (`id`, `video_id`, `create_time`) VALUES ('2', '1', '2018-12-12 16:59:17');
INSERT INTO `test`.`t_praise` (`id`, `video_id`, `create_time`) VALUES ('3', '2', '2018-12-12 16:59:24');
INSERT INTO `test`.`t_praise` (`id`, `video_id`, `create_time`) VALUES ('4', '2', '2018-12-12 16:59:30');
INSERT INTO `test`.`t_praise` (`id`, `video_id`, `create_time`) VALUES ('5', '2', '2018-12-12 16:59:38');
INSERT INTO `test`.`t_praise` (`id`, `video_id`, `create_time`) VALUES ('6', '2', '2018-12-12 16:59:47');

mysql> select * from t_video;
+----+-----------+-----------+
| id | name      | describe  |
+----+-----------+-----------+
|  1 | 自拍      | 自拍      |
|  2 | 小猫咪    | 小猫咪    |
|  3 | 小狗狗    | 小狗狗    |
|  4 | 驴蛋蛋    | 驴蛋蛋    |
|  5 | 小脑腐    | 小脑腐    |
+----+-----------+-----------+
5 rows in set (0.00 sec)

mysql> select * from t_comment;
+----+----------+-----------------+
| id | video_id | comment         |
+----+----------+-----------------+
|  1 |        1 | 啊好好看        |
|  2 |        1 | 啊好丑啊        |
|  3 |        1 | 你是魔鬼吗      |
|  4 |        2 | 喵喵喵          |
|  5 |        2 | 喵喵喵          |
|  6 |        3 | 汪汪汪          |
+----+----------+-----------------+
6 rows in set (0.00 sec)

mysql> select * from t_praise;
+----+----------+---------------------+
| id | video_id | create_time         |
+----+----------+---------------------+
|  1 |        1 | 2018-12-12 16:59:08 |
|  2 |        1 | 2018-12-12 16:59:17 |
|  3 |        2 | 2018-12-12 16:59:24 |
|  4 |        2 | 2018-12-12 16:59:30 |
|  5 |        2 | 2018-12-12 16:59:38 |
|  6 |        2 | 2018-12-12 16:59:47 |
+----+----------+---------------------+
6 rows in set (0.00 sec)

-- group by c.video_id 会导致左表没有评论的视频（在右表中不存在记录的）将不会被展示，他们会统一归到c.video_id为null的这一条记录中
-- 统计c.video_id c表中video_id为null的肯定为空，所以就是0
SELECT v.id,v.`name`,c.video_id,COUNT(c.video_id) from t_video v LEFT JOIN t_comment c ON c.video_id=v.id GROUP BY c.video_id;

mysql> SELECT v.id,v.`name`,c.video_id,COUNT(c.video_id) from t_video v LEFT JOIN t_comment c ON c.video_id=v.id GROUP BY c.video_id;
+----+-----------+----------+-------------------+
| id | name      | video_id | COUNT(c.video_id) |
+----+-----------+----------+-------------------+
|  4 | 驴蛋蛋    |     NULL |                 0 |
|  1 | 自拍      |        1 |                 3 |
|  2 | 小猫咪    |        2 |                 2 |
|  3 | 小狗狗    |        3 |                 1 |
+----+-----------+----------+-------------------+
4 rows in set (0.00 sec)

-- group by c.video_id 会导致左表没有评论的视频（在右表中不存在记录的）将不会被展示，他们会统一归到c.video_id为null的这一条记录中
-- 统计v.id v表中有两条没有评论的记录被归到了 c.video_id为null的这条记录中，所以count为2
SELECT v.id,v.`name`,c.video_id,COUNT(v.id) from t_video v LEFT JOIN t_comment c ON c.video_id=v.id GROUP BY c.video_id;

mysql> SELECT v.id,v.`name`,c.video_id,COUNT(v.id) from t_video v LEFT JOIN t_comment c ON c.video_id=v.id GROUP BY c.video_id;
+----+-----------+----------+-------------+
| id | name      | video_id | COUNT(v.id) |
+----+-----------+----------+-------------+
|  4 | 驴蛋蛋    |     NULL |           2 |
|  1 | 自拍      |        1 |           3 |
|  2 | 小猫咪    |        2 |           2 |
|  3 | 小狗狗    |        3 |           1 |
+----+-----------+----------+-------------+
4 rows in set (0.00 sec)

-- group by v.id 按v.id 分组 统计 v.id 那么count至少就是1
SELECT v.id,v.`name`,c.video_id,COUNT(v.id) from t_video v LEFT JOIN t_comment c ON c.video_id=v.id GROUP BY v.id;

mysql> SELECT v.id,v.`name`,c.video_id,COUNT(v.id) from t_video v LEFT JOIN t_comment c ON c.video_id=v.id GROUP BY v.id;
+----+-----------+----------+-------------+
| id | name      | video_id | COUNT(v.id) |
+----+-----------+----------+-------------+
|  1 | 自拍      |        1 |           3 |
|  2 | 小猫咪    |        2 |           2 |
|  3 | 小狗狗    |        3 |           1 |
|  4 | 驴蛋蛋    |     NULL |           1 |
|  5 | 小脑腐    |     NULL |           1 |
+----+-----------+----------+-------------+
5 rows in set (0.00 sec)

-- 这个才是正确的
SELECT v.id,v.`name`,c.video_id,COUNT(c.video_id) from t_video v LEFT JOIN t_comment c ON c.video_id=v.id GROUP BY v.id;

mysql> SELECT v.id,v.`name`,c.video_id,COUNT(c.video_id) from t_video v LEFT JOIN t_comment c ON c.video_id=v.id GROUP BY v.id;
+----+-----------+----------+-------------------+
| id | name      | video_id | COUNT(c.video_id) |
+----+-----------+----------+-------------------+
|  1 | 自拍      |        1 |                 3 |
|  2 | 小猫咪    |        2 |                 2 |
|  3 | 小狗狗    |        3 |                 1 |
|  4 | 驴蛋蛋    |     NULL |                 0 |
|  5 | 小脑腐    |     NULL |                 0 |
+----+-----------+----------+-------------------+
5 rows in set (0.00 sec)

-- 再连点赞表
SELECT v.id,v.`name`,p.video_id,COUNT(p.video_id) from t_video v LEFT JOIN t_praise p ON p.video_id=v.id GROUP BY v.id;

mysql> SELECT v.id,v.`name`,p.video_id,COUNT(p.video_id) from t_video v LEFT JOIN t_praise p ON p.video_id=v.id GROUP BY v.id;
+----+-----------+----------+-------------------+
| id | name      | video_id | COUNT(p.video_id) |
+----+-----------+----------+-------------------+
|  1 | 自拍      |        1 |                 2 |
|  2 | 小猫咪    |        2 |                 4 |
|  3 | 小狗狗    |     NULL |                 0 |
|  4 | 驴蛋蛋    |     NULL |                 0 |
|  5 | 小脑腐    |     NULL |                 0 |
+----+-----------+----------+-------------------+
5 rows in set (0.00 sec)

下边这个sql语句怎么看都是没错的是吧，但是！！！我们拆开来看！
SELECT v.id,v.`name`,c.video_id AS cvid , p.video_id AS pvid,COUNT(c.video_id) AS comments,COUNT(p.video_id)AS praises from t_video v LEFT JOIN t_comment c ON c.video_id=v.id LEFT JOIN t_praise p ON p.video_id=v.id GROUP BY v.id;


mysql> SELECT v.id,v.`name`,c.video_id AS cvid , p.video_id AS pvid,COUNT(c.video_id) AS comments,COUNT(p.video_id)AS praises from t_video v LEFT JOIN t_comment c ON c.video_id=v.id LEFT JOIN t_praise p ON p.video_id=v.id GROUP BY v.id;
+----+-----------+------+------+----------+---------+
| id | name      | cvid | pvid | comments | praises |
+----+-----------+------+------+----------+---------+
|  1 | 自拍      |    1 |    1 |        6 |       6 |
|  2 | 小猫咪    |    2 |    2 |        8 |       8 |
|  3 | 小狗狗    |    3 | NULL |        1 |       0 |
|  4 | 驴蛋蛋    | NULL | NULL |        0 |       0 |
|  5 | 小脑腐    | NULL | NULL |        0 |       0 |
+----+-----------+------+------+----------+---------+
5 rows in set (0.00 sec)

mysql> SELECT v.id,v.`name`,c.`comment` from t_video v LEFT JOIN t_comment c ON c.video_id=v.id;
+----+-----------+-----------------+
| id | name      | comment         |
+----+-----------+-----------------+
|  1 | 自拍      | 啊好好看        |
|  1 | 自拍      | 啊好丑啊        |
|  1 | 自拍      | 你是魔鬼吗      |
|  2 | 小猫咪    | 喵喵喵          |
|  2 | 小猫咪    | 喵喵喵          |
|  3 | 小狗狗    | 汪汪汪          |
|  4 | 驴蛋蛋    | NULL            |
|  5 | 小脑腐    | NULL            |
+----+-----------+-----------------+
8 rows in set (0.00 sec)

mysql> SELECT v.id,v.`name`,p.id FROM t_video v LEFT JOIN t_praise p ON p.video_id=v.id;
+----+-----------+------+
| id | name      | id   |
+----+-----------+------+
|  1 | 自拍      |    1 |
|  1 | 自拍      |    2 |
|  2 | 小猫咪    |    3 |
|  2 | 小猫咪    |    4 |
|  2 | 小猫咪    |    5 |
|  2 | 小猫咪    |    6 |
|  3 | 小狗狗    | NULL |
|  4 | 驴蛋蛋    | NULL |
|  5 | 小脑腐    | NULL |
+----+-----------+------+
9 rows in set (0.00 sec)

mysql> SELECT v.id,v.`name`,c.video_id AS cvid , p.video_id AS pvid,c.`comment` ,p.id from t_video v
    -> LEFT JOIN t_comment c ON c.video_id=v.id LEFT JOIN t_praise p ON p.video_id=v.id ;
+----+-----------+------+------+-----------------+------+
| id | name      | cvid | pvid | comment         | id   |
+----+-----------+------+------+-----------------+------+
|  1 | 自拍      |    1 |    1 | 啊好好看        |    1 |
|  1 | 自拍      |    1 |    1 | 啊好好看        |    2 |
|  1 | 自拍      |    1 |    1 | 啊好丑啊        |    1 |
|  1 | 自拍      |    1 |    1 | 啊好丑啊        |    2 |
|  1 | 自拍      |    1 |    1 | 你是魔鬼吗      |    1 |
|  1 | 自拍      |    1 |    1 | 你是魔鬼吗      |    2 |
|  2 | 小猫咪    |    2 |    2 | 喵喵喵          |    3 |
|  2 | 小猫咪    |    2 |    2 | 喵喵喵          |    4 |
|  2 | 小猫咪    |    2 |    2 | 喵喵喵          |    5 |
|  2 | 小猫咪    |    2 |    2 | 喵喵喵          |    6 |
|  2 | 小猫咪    |    2 |    2 | 喵喵喵          |    3 |
|  2 | 小猫咪    |    2 |    2 | 喵喵喵          |    4 |
|  2 | 小猫咪    |    2 |    2 | 喵喵喵          |    5 |
|  2 | 小猫咪    |    2 |    2 | 喵喵喵          |    6 |
|  3 | 小狗狗    |    3 | NULL | 汪汪汪          | NULL |
|  4 | 驴蛋蛋    | NULL | NULL | NULL            | NULL |
|  5 | 小脑腐    | NULL | NULL | NULL            | NULL |
+----+-----------+------+------+-----------------+------+
17 rows in set (0.00 sec)

所以会出现count全都变成了两个count的乘积的情况
正确的sql如下

SELECT t.*,COUNT(p.id) FROM (SELECT v.id,v.`name`,c.video_id,COUNT(c.video_id) AS comments from t_video v LEFT JOIN t_comment c ON c.video_id=v.id GROUP BY v.id)t LEFT JOIN t_praise p ON p.video_id=t.id GROUP BY t.id;

mysql> SELECT t.*,COUNT(p.id) FROM (SELECT v.id,v.`name`,c.video_id,COUNT(c.video_id) AS comments from t_video v LEFT JOIN t_comment c ON c.video_id=v.id GROUP BY v.id)t LEFT JOIN t_praise p ON p.video_id=t.id GROUP BY t.id;
+----+-----------+----------+----------+-------------+
| id | name      | video_id | comments | COUNT(p.id) |
+----+-----------+----------+----------+-------------+
|  1 | 自拍      |        1 |        3 |           2 |
|  2 | 小猫咪    |        2 |        2 |           4 |
|  3 | 小狗狗    |        3 |        1 |           0 |
|  4 | 驴蛋蛋    |     NULL |        0 |           0 |
|  5 | 小脑腐    |     NULL |        0 |           0 |
+----+-----------+----------+----------+-------------+
5 rows in set (0.00 sec)

一步一步来是这样的

mysql> SELECT t.*,p.id FROM (SELECT v.id,v.`name`,c.video_id,COUNT(c.video_id) AS comments from t_video v LEFT JOIN t_comment c ON c.video_id=v.id GROUP BY v.id)t LEFT JOIN t_praise p ON p.video_id=t.id ;
+----+-----------+----------+----------+------+
| id | name      | video_id | comments | id   |
+----+-----------+----------+----------+------+
|  1 | 自拍      |        1 |        3 |    1 |
|  1 | 自拍      |        1 |        3 |    2 |
|  2 | 小猫咪    |        2 |        2 |    3 |
|  2 | 小猫咪    |        2 |        2 |    4 |
|  2 | 小猫咪    |        2 |        2 |    5 |
|  2 | 小猫咪    |        2 |        2 |    6 |
|  3 | 小狗狗    |        3 |        1 | NULL |
|  4 | 驴蛋蛋    |     NULL |        0 | NULL |
|  5 | 小脑腐    |     NULL |        0 | NULL |
+----+-----------+----------+----------+------+
9 rows in set (0.00 sec)

hiahiahia~

MySQL左连接两张表分别count两张表数据时数量变为乘积

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读