Shulou(Shulou.com)06/01 Report--

This article mainly explains the common problems of left-join in MySQL join table query, the content is clear, interested friends can learn, I believe it will be helpful after reading it.

Overview

For small and medium-sized projects, join table query is a common operation, especially when making reports. However, when you proofread the data, did you find a hole? This article repeats the common pits for mysql common join table queries.

Basic environment

Construction table sentence

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 leader'); insert into `role` VALUES (4, 'team leader'); DROP TABLE IF EXISTS `user` CREATE TABLE `user` (`id` int (11) NOT NULL AUTO_INCREMENT, `user_ id` int (11) NOT NULL COMMENT 'role 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, 'Chief Wang', 1); insert into `user`VALUES (6,3, 'Chief Li', 1); insert into `user`VALUES (7,3, 'Chief Lu', 2); insert into `user`VALUES (8,3, 'Xing Chief', 1); insert into `user`VALUES (9,4, 'team Leader', 2) Insert into `user`VALUES (10,4, 'team leader Zhao', 2); insert into `user`VALUES (11,4, 'team leader Ji', 1)

The data are as follows

Mysql > select * from role;+----+-+ | id | role_name | +-+ | 1 | Administrator | 2 | General Manager | 3 | Section Chief | | 4 | team Leader | +-+ 4 rows in set (0.00 sec) mysql > select * from user +-+ | id | role_id | user_name | sex | +-- + | 1 | 1 | admin | 1 | 2 | 2 | Manager Wang | 1 | | 3 | 2 | Manager Li | 2 | 4 | 2 | Manager Zhang | 2 | | 5 | 3 | Wang Section Chief | 1 | | 6 | 3 | Li Section Chief | 1 | 7 | 3 | Lu Ke Chief | 2 | 8 | 3 | Xing Section Chief | 1 | 9 | 4 | Fan Leader | 2 | 10 | 4 | Zhao Leader | 2 | 11 | 4 | Leader Ji | 1 | + | -+ 11 rows in set (0.00 sec)

Basic business

Simple information report: query 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 | female | 4 | Manager Zhang | female | 5 | Chief Wang | male | 6 | Chief Li | male | | | 7 | Chief Lv Ke | female | | 8 | Section Chief Xing | male | | 9 | Leader Fan | female | 10 | Leader Zhao | female | | 11 | Leader Ji | male | +-+ |

Query the name of each role and the number of women in the corresponding personnel

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 +-administrator | 0 | 2 | General Manager | 2 | 3 | Section Chief | 1 | 4 | Group Leader | 2 | +-+ 4 rows in set (0.00 sec)

What happens if we change the condition of gender filtering to where operation?

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 +-2 | General Manager | 2 | 3 | Section Chief | 1 | 4 | Group Leader | 2 | +-+ 3 rows in set (0.00 sec)

You can see here that the role data is incomplete.

Find out the number of employees with the role of general manager

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-> r.role_name = 'General Manager'-> GROUP BY-> r.role_name-> ORDER BY-> r.id ASC +-+ | id | role | sex | +-+ | 2 | General Manager | 3 | +-+ 1 row in set (0.00 sec)

Also change the filter condition from where to on

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 r.role_name = 'General Manager'-> GROUP BY-> r.role_name-> ORDER BY-> r.id ASC +-1 | Administrator | 0 | 2 | General Manager | 3 | 3 | Section Chief | 0 | | 4 | Group Leader | 0 | + 4 rows in set (0.00 sec)

You can see here that the data is redundant.

Summary

In the 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 results can be neither more nor less, just right.

After reading the above content, do you have a further understanding of left-join common questions in MySQL linked table query? if you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.