user表的数据
1
2
3
4
5
6
7
8
9
| mysql> select * from user;
+----+----------+
| id | username |
+----+----------+
| 1 | 1111 |
| 2 | admin |
| 3 | test |
+----+----------+
3 rows in set (0.00 sec)
|
note表的数据
1
2
3
4
5
6
7
8
9
10
11
| mysql> select * from note;
+----+--------+---------+
| id | title | user_id |
+----+--------+---------+
| 1 | 111111 | 1 |
| 2 | 2222 | 2 |
| 3 | 333 | 3 |
| 4 | 333222 | 2 |
| 5 | 333222 | 5 |
+----+--------+---------+
5 rows in set (0.00 sec)
|
如果直接左连接，出来的结果并非以左边为唯一列，因为关联on的note.user_id不是唯一列，所以结果user表里有两个2
1
2
3
4
5
6
7
8
9
10
| mysql> select * from user left join note on note.user_id = user.id;
+----+----------+------+--------+---------+
| id | username | id | title | user_id |
+----+----------+------+--------+---------+
| 1 | 1111 | 1 | 111111 | 1 |
| 2 | admin | 2 | 2222 | 2 |
| 3 | test | 3 | 333 | 3 |
| 2 | admin | 4 | 333222 | 2 |
+----+----------+------+--------+---------+
4 rows in set (0.00 sec)
|
先将note表唯一列化，在用user去连接这个表，出来的结果就是 user表id唯一了
1
2
3
4
5
6
7
8
9
| mysql> select * from user left join (select * from note group by user_id)as n on n.user_id = user.id;
+----+----------+------+--------+---------+
| id | username | id | title | user_id |
+----+----------+------+--------+---------+
| 1 | 1111 | 1 | 111111 | 1 |
| 2 | admin | 2 | 2222 | 2 |
| 3 | test | 3 | 333 | 3 |
+----+----------+------+--------+---------+
3 rows in set (0.00 sec)
|