有两个表如下结构：

test_join_a

+----+----------+------+
| id | username | flag |
+----+----------+------+
|  1 | a        |    1 |
|  2 | a        |    0 |
|  3 | b        |    1 |
|  4 | c        |    1 |
+----+----------+------+

test_join_b

+------+-----------+
| idb  | usernameb |
+------+-----------+
|   11 | a         |
|   12 | b         |
|   13 | b         |
+------+-----------+

现在需要对两个表进行连接查询，最后希望得到的结果是表a中每一行连接表b中的idb最大的一行，没有的置idb为空。

如果正常使用左连接 left join，

select a.username,a.flag,b.usernameb,b.idb from test_join_a a left join test_join_b b on a.username=b.usernameb;

会得到以下结果:

+----------+------+-----------+------+
| username | flag | usernameb | idb  |
+----------+------+-----------+------+
| a        |    1 | a         |   11 |
| a        |    0 | a         |   11 |
| b        |    1 | b         |   12 |
| b        |    1 | b         |   13 |
| c        |    1 | NULL      | NULL |
+----------+------+-----------+------+

很明显，a表中的username为b的数据被连接了两次，产生了两行，这个不是我所希望的结果，所以进行以下处理：

select a.username,a.flag,b.usernameb,b.idb from test_join_a a left join (select * from test_join_b c where c.idb=(select max(d.idb) from test_join_b d where c.usernameb=d.usernameb group by usernameb)) b on a.username=b.usernameb;

最终会得到如下数据：

+----------+------+-----------+------+
| username | flag | usernameb | idb  |
+----------+------+-----------+------+
| a        |    1 | a         |   11 |
| a        |    0 | a         |   11 |
| b        |    1 | b         |   13 |
| c        |    1 | NULL      | NULL |
+----------+------+-----------+------+

怎么样，没有重复行了吧~