表结构如下:
mysql> select * from dup_view;
+---------+----------+----------+-----------+
| call_id | s_msisdn | o_msisdn | o_call_id |
+---------+----------+----------+-----------+
| 1 | 123456 | 654321 | 2 |
| 2 | 654321 | 123456 | 1 |
| 3 | 98765 | 567890 | 4 |
| 4 | 567890 | 98765 | 3 |
| 5 | 123456 | 999999 | 0 |
+---------+----------+----------+-----------+
5 rows in set (0.00 sec)如何写查询语句取出表中没有重复的记录,重复的记录是指call_id和o_call_id相互相等的两条记录。如 1和2, 3和4。期望的查询结果是1,3,5。
mysql> select * from dup_view;
+---------+----------+----------+-----------+
| call_id | s_msisdn | o_msisdn | o_call_id |
+---------+----------+----------+-----------+
| 1 | 123456 | 654321 | 2 |
| 2 | 654321 | 123456 | 1 |
| 3 | 98765 | 567890 | 4 |
| 4 | 567890 | 98765 | 3 |
| 5 | 123456 | 999999 | 0 |
+---------+----------+----------+-----------+
5 rows in set (0.00 sec)如何写查询语句取出表中没有重复的记录,重复的记录是指call_id和o_call_id相互相等的两条记录。如 1和2, 3和4。期望的查询结果是1,3,5。
select * from tt a where not exists(select 1 from tt where a.call_id=o_call_id and a.call_id=o_call_id )
union all
select * from tt a where not exists(select 1 from tt where a.call_id=o_call_id and a.call_id=o_call_id and a.call_id>call_id)
call_id < s_msisdn 字段的值成立么?
若是成立的话,就很简单了,我就不写出来了... = =
select * from tt a where not exists(select 1 from tt where a.call_id=o_call_id and a.call_id=o_call_id and a.call_id>call_id)