mysql - 在不使用 where 的情况下选择连接记录，省略垃圾关系数据

Question

说

Table A             Table AB         Table B
+----+---------+    +-----+-----+    +----+-----+
| id | name    |    | ida | idb |    | id | age |
+----+---------+    +-----+-----+    +----+-----+
|  1 |   'one' |    |   1 |   3 |    |  3 |   3 |
+----+---------+    +-----+-----+    +----+-----+
|  2 |   'two' |    |   2 |   4 |    |  5 |   5 |
+----+---------+    +-----+-----+    +----+-----+
|  3 | 'three' |    |   2 |   5 |
+----+---------+    +-----+-----+

What I want           What I get
+---------+------+    +---------+------+
| name    | age  |    | name    | age  |
+---------+------+    +---------+------+
|   'one' |    3 |    |   'one' |    3 |
+---------+------+    +---------+------+
|   'two' |    5 |    |   'two' |    5 |
+---------+------+    +---------+------+
| 'three' | NULL |    |   'two' | NULL |
+---------+------+    +---------+------+
                      | 'three' | NULL |
                      +---------+------+

我的sql是

SELECT A.name, B.age
FROM A
LEFT JOIN AB
  ON A.id = AB.ida
LEFT JOIN B
  ON AB.idb = B.id

约束:出于特定原因我不想使用Where 语句，也不想出于性能原因使用子查询。

有没有一种方法我只能获取关系 AB 与仅使用 JOIN/ON 且不使用子查询而存在的内容匹配的记录？

Answer 1

使用分组。

SELECT A.name, Max(B.age)
FROM A 
LEFT JOIN AB 
  ON A.id = AB.ida 
LEFT JOIN B 
  ON AB.idb = B.id 
GROUP BY a.id, a.Name 
order by a.id

或者您可以使用右连接。

select a.name, b.age
from 
    b 
    inner join ab on b.id = ab.idb
    right join a on ab.ida = a.id