An overview of the

For small – and medium-sized projects, joint table queries are a common operation, especially when making reports. However, when you proofread the data, did you find any errors? This article reproduces common pits in mysql common table queries.

Based on the environment

  1. Build table statements
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` VARCHAR(50) DEFAULT NULL COMMENT 'Role name',
  PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Role table';


insert into `role` VALUES(1.'Administrator');
insert into `role` VALUES(2.'General Manager');
insert into `role` VALUES(3.'section');
insert into `role` VALUES(4.'leader');

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
    `role_id` int(11) NOT NULL COMMENT 'character id'.`user_name` VARCHAR(50) DEFAULT NULL COMMENT 'Username'.`sex` int(1) DEFAULT 0 COMMENT 'gender',
  PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User table';

insert into `user` VALUES(1.1.'admin'.1);
insert into `user` VALUES(2.2.'Manager Wang'.1);
insert into `user` VALUES(3.2.'Manager Li'.2);
insert into `user` VALUES(4.2.'Manager Zhang'.2);
insert into `user` VALUES(5.3.Section Chief Wang.1);
insert into `user` VALUES(6.3.'Section Chief Li'.1);
insert into `user` VALUES(7.3.'Chief Lu'.2);
insert into `user` VALUES(8.3.'Chief Xing'.1);
insert into `user` VALUES(9.4.'Group Leader Fan'.2);
insert into `user` VALUES(10.4.'Group Leader Zhao'.2);
insert into `user` VALUES(11.4.'Leader Hiji'.1);
Copy the code
  1. The following data
mysql> select * from role; + - + -- -- -- -- -- -- -- -- -- -- -- + | | id role_name | + - + -- -- -- -- -- -- -- -- -- -- -- + | 1 | | administrator 2 general manager | | | | 3 | section chief | | | | 4 leader + - + -- -- -- -- -- -- -- -- -- -- - + 4 rows in the set (0.00 SEC) mysql > select * from the user. +----+---------+-----------+------+ | id | role_id | user_name | sex | +----+---------+-----------+------+ | 1 | 1 | Admin | 1 | | 2 | 2 | manager wang | 1 | | 2 | 3 | manager li | 2 | | | 2 | 4 manager zhang | | 2 | 3 | | 5 Wang Kechang | 1 | | | 3 | 6 section li | 1 | | | 3 July 8 | | Lv Ke long | 2 | | 3 | section chief xing | 1 | | | | 9 4 van leader | 2 | | | | 4 zhao leader | 2 | | | | 4 11 ji leader | | 1 +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 11 rows in the set (0.00 SEC)Copy the code

Basic business

Simple information report: Queries user information

Mysql > SELECT -> id, -> user_name AS 'name ', CASE WHEN sex = 1 THEN 'male' WHEN sex = 2 THEN 'female' ELSE 'unknown' END) AS 'gender' -> FROM -> USER; + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | | | id name gender + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- + | | 1 admin male | | | 2 | manager wang male | | | 3 | manager li | women | | | 4 female manager zhang | | | | 5 Wang Kechang male | | | | 6 section li male | | | | 7 Lv Ke long female | | | | | section chief xing 8 male | | | 9 van leader female | | | | zhao leader female | | | 11 | | her leader male | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- +Copy the code

Query the name of each role and corresponding personnelwomenThe number of

mysql> SELECT -> r.id, -> r.role_name AS role, -> count( u.sex ) AS sex -> FROM -> role r -> LEFT JOIN USER u ON r.id = u.role_id -> AND u.sex = 2 -> GROUP BY -> r.role_name -> ORDER BY -> r.id ASC; + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id role | sex | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | 0 | 1 | administrator | 2 | 2 | | general manager | 3 | section chief | 1 | | 4 | | | + - + 2 team leader -- -- -- -- -- -- -- -- -- -- - + - + 4 rows in the set (0.00 SEC)Copy the code

What if we changed the gender filter to where?

mysql> SELECT -> r.id, -> r.role_name AS role, -> count( u.sex ) AS sex -> FROM -> role r -> LEFT JOIN USER u ON r.id = u.role_id -> WHERE -> u.sex = 2 -> GROUP BY -> r.role_name -> ORDER BY -> r.id ASC; + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id role | sex | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | 2 | 2 | general manager | 3 | section chief | 1 | | 2 | | | 4 leader +----+-----------+-----+ 3 rows in set (0.00 SEC)Copy the code

Here you can see that the character data is incomplete.

Find the role asThe general managerNumber of employees

mysql> SELECT -> r.id, -> r.role_name AS role, U.s ex AS sex -> FROM -> role R -> LEFT JOIN USER U ON R.id = U.ral_id -> WHERE -> r.role_name = 'general manager' -> GROUP BY -> r.role_name -> ORDER BY -> r.id ASC; + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id role | sex | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | 2 | 3 | | general manager + + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + 1 row in Set (0.00 SEC)Copy the code

Also change the filter condition from where to ON

mysql> SELECT -> r.id, -> r.role_name AS role, AS sex -> FROM -> role R -> LEFT JOIN USER U ON R.ID = U.ral_id -> AND r.role_name = 'general manager' -> GROUP BY -> r.role_name -> ORDER BY -> r.id ASC; + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id role | sex | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | 0 | 1 | administrator | 2 | 3 | | general manager | 3 | section chief | 0 | | 4 | | | 0 + team leader - + -- -- -- -- -- -- -- -- -- -- - + - + 4 rows in the set (0.00 SEC)Copy the code

You can see here that the data is redundant

conclusion

In a left JOIN statement, the left table filter must be placed in the WHERE condition and the right table filter must be placed in the ON condition so that the result is just right.

That’s the end of this article. Thank you for reading, if you feel good, please pay attention to the public number [when I meet you] support.