Reference link

ORACLE Get the first and last values after grouping and sorting

practice

I met you recently sql The problem of .
wx_user There are some problems with your mobile phone number , A string login occurred . Originally, a user has a mobile number , Now it has become multiple .
The user's initial mobile number is in the login resume WX_USER_LOGIN The first item in the search according to its ID number （ Time ）.
Based on the above analysis ,sql as follows ：

select u.id id,u.id_no idno,u.mobile wrongmobile,t.id_no tin, t.mobile truemobile from wx_user u,
  (   select id_no,mobile from (
         select
                 t1.id_no,
                 t1.mobile,
                 row_number() over(PARTITION BY t1.id_no order by t1.create_time) rowss1
                 from (select * from WX_USER_LOGIN t where id_no in 
                      (select id_no from wx_user where mobile in (
                       select mobile from wx_user group by mobile having count (*)>1))
                       ) t1
       
  )
  where rowss1 = '1') t
  where u.id_no in(t.id_no)

First , Find out the wrong mobile phone number and the corresponding ID card ：

select id_no from wx_user where mobile in (
                       select mobile from wx_user group by mobile having count (*)>1)

Find out the login resume according to the ID number ：

select * from WX_USER_LOGIN t where id_no in 
                      (select id_no from wx_user where mobile in (
                       select mobile from wx_user group by mobile having count (*)>1))

The above record is table t1
Group sort ：

select
                 t1.id_no,
                 t1.mobile,
                 row_number() over(PARTITION BY t1.id_no order by t1.create_time) rowss1
                 from t1

Take the first item after grouping and sorting

select id_no,mobile from (
         select
                 t1.id_no,
                 t1.mobile,
                 row_number() over(PARTITION BY t1.id_no order by t1.create_time) rowss1
                 from (select * from WX_USER_LOGIN t where id_no in 
                      (select id_no from wx_user where mobile in (
                       select mobile from wx_user group by mobile having count (*)>1))
                       ) t1
       
  )
  where rowss1 = '1'

The first item after grouping and sorting is used as a table t, Combine the original table and table t, Get all the required fields ：

select u.id id,u.id_no idno,u.mobile wrongmobile,t.id_no tin, t.mobile truemobile from wx_user u,t  where u.id_no in(t.id_no)

select * from a,b where a.id = b.id The meaning changed , This is equivalent to : select * from a inner join b on a.id = b.id. That is, inner connection .

Postscript

Used in grouping PARTITION, It also means partition table . Next, learn the partition table .