MySQL左连接一个表到自己
问题描述：
请考虑下表"mmm":
Please consider the following table 'mmm':
select * from mmm;
输出:
+-------+-------+------+
| texto | value | n |
+-------+-------+------+
| aaa | 10 | 1 |
| aab | 10 | 1 |
| aaa | 11 | 1 |
| aab | 11 | 1 |
| aaa | 10 | 2 |
+-------+-------+------+
命令:
select a.*, '--', b.*
from mmm a
left join mmm b on (a.n=b.n)
where a.value < b.value
and a.texto ='aaa'
and b.texto='aab';
返回:
+-------+-------+------+----+-------+-------+------+
| texto | value | n | -- | texto | value | n |
+-------+-------+------+----+-------+-------+------+
| aaa | 10 | 1 | -- | aab | 11 | 1 |
+-------+-------+------+----+-------+-------+------+
很好.但是我想要的是这样的:
That's fine. But what I want is something like:
+-------+-------+------+----+-------+-------+------+
| texto | value | n | -- | texto | value | n |
+-------+-------+------+----+-------+-------+------+
| aaa | 10 | 1 | -- | aab | 11 | 1 |
+-------+-------+------+----+-------+-------+------+
| aaa | 10 | 2 | -- | NULL | NULL | NULL |
+-------+-------+------+----+-------+-------+------+
答
select a.*, '--', b.*
from mmm a
left join mmm b on (a.n=b.n)
where (a.value < b.value or b.value is null)
and a.texto ='aaa'
and (b.texto='aab' or b.textto is null);
或:
select a.*, '--', b.*
from mmm a
left join mmm b on (a.n=b.n and a.value < b.value and b.texto = 'aab')
where a.texto ='aaa' ;