select col1,col2 from 一个表 t where not exists (select 1 from 一个表 where col1=t.co2 and col2=t.col1 and col2>col1)
mysql> select * from test; +------+------+ | id | col2 | +------+------+ | 1 | 2 | | 2 | 1 | | 1 | 3 | +------+------+ 3 rows in set (0.00 sec) mysql> select id, col2 from test a where exists( select col2, id from test b where b.col2=a.id and b.id=a.col2 and a.id<=a.col2) union select id, col2 from test a where not exists( select col2, id from test b where b.col2=a.id and b.id=a.col2); +------+------+ | id | col2 | +------+------+ | 1 | 2 | | 1 | 3 | +------+------+ 2 rows in set (0.00 sec)
贴要求结果出来看看,表中是否有唯一标识的字段 select * from tt a where exists(select 1 from tt where a.f1=f2 and a.f2=f1 and a.id>id)
from 一个表 t
where not exists (select 1 from 一个表 where col1=t.co2 and col2=t.col1 and col2>col1)
+------+------+
| id | col2 |
+------+------+
| 1 | 2 |
| 2 | 1 |
| 1 | 3 |
+------+------+
3 rows in set (0.00 sec)
mysql> select id, col2 from test a where exists( select col2, id from test b where b.col2=a.id and b.id=a.col2 and a.id<=a.col2) union select id, col2 from test a where not exists( select col2, id from test b where b.col2=a.id and b.id=a.col2);
+------+------+
| id | col2 |
+------+------+
| 1 | 2 |
| 1 | 3 |
+------+------+
2 rows in set (0.00 sec)
select * from tt a where exists(select 1 from tt where a.f1=f2 and a.f2=f1 and a.id>id)