TABLE a:
mobile p_id sort
---------------------------------
13588849999 1 99
13588849999 2 88
13588849999 3 77
13588849998 1 87
13588849998 2 99
13588849997 9 105
13588849997 10 6要先取相同mobile里面p_id最小的,再进行sort的倒序进行排序
结果为:
mobile p_id sort
---------------------------------
13588849997 9 105
13588849999 1 99
13588849998 1 87
这个sql要如果写?
mobile p_id sort
---------------------------------
13588849999 1 99
13588849999 2 88
13588849999 3 77
13588849998 1 87
13588849998 2 99
13588849997 9 105
13588849997 10 6要先取相同mobile里面p_id最小的,再进行sort的倒序进行排序
结果为:
mobile p_id sort
---------------------------------
13588849997 9 105
13588849999 1 99
13588849998 1 87
这个sql要如果写?
(
select mobile,p_id,sort,row_number() over(partition by mobile order by p_id) r
from a
)where r=1
order by sort desc;
(select mobile,min(p_id) from table_name group by mobile)
order by sort desc
如果用rank可以查询最小值有重复,
比如还有一条:13588849997 9 100select mobile,p_id,sort from
(
select mobile,p_id,sort,rank() over(partition by mobile order by p_id) r
from a
)where r=1
order by sort desc;
WHERE p_id = (SELECT MIN(p_id) FROM a a2
WHERE a2.mobile = a1.mobile)
ORDER BY SORT DESC ;试试看结果吧。该没问题。