推荐还是使用select user_mobile,min(service_id) as service_id from t_user group by user_mobile order by service_id,user_mobile; 的方式mysql> select * -> from t_user a -> where not exists (select 1 from t_user where user_mobile=a.user_mobile and service_id<a.service_id);的方式效率比较差
select user_mobile,min (service_id ) from t_user group by user_mobile order by min (service_id );
4 5 满足条件 ?
select 1 from t_user where user_mobile =a.user_mobile and service_id<a.service_id
我要得到的结果是按照ID排序,等于1的在上面,既1,2,3,6.等于2的再下面,并且ID=1的不能列出来,既ID=2的4,5
整体排列下来就是
ID=1
1,2,3,6,
ID=2直接补充一下,service_id这个字段里面不光有1跟2,还有3跟4,怎么能仅列出1跟2的,不要3跟4的呢?
4,5
这下明白了吧?
select * from t_user t where not exists (
select 1 from t_user where user_mobile =t.user_mobile and service_id<t.service_id)
order by service_id;
from t_user
order by service_id,user_mobile;
select user_mobile,min(service_id) as service_id
from t_user
group by user_mobile
order by service_id,user_mobile;
+-------------+------------+
| user_mobile | service_id |
+-------------+------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 2 |
| 5 | 2 |
| 6 | 1 |
| 6 | 2 |
+-------------+------------+
10 rows in set (0.06 sec)mysql> select *
-> from t_user a
-> where not exists (select 1 from t_user where user_mobile=a.user_mobile and service_id<a.service_id);
+-------------+------------+
| user_mobile | service_id |
+-------------+------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 1 |
+-------------+------------+
6 rows in set (0.06 sec)mysql> select user_mobile,min(service_id) as service_id
-> from t_user
-> group by user_mobile;
+-------------+------------+
| user_mobile | service_id |
+-------------+------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
| 6 | 1 |
+-------------+------------+
6 rows in set (0.00 sec)
from t_user
group by user_mobile
order by service_id,user_mobile;
的方式mysql> select *
-> from t_user a
-> where not exists (select 1 from t_user where user_mobile=a.user_mobile and service_id<a.service_id);的方式效率比较差