参考链接

ORACLE分组排序后获取第一条和最后一条值

实践

最近遇到的sql的问题。
wx_user的手机号有部分有问题，发生了串登录。原本一个用户一个手机号，现在变成了多个。
该用户的初始手机号是在登录履历表WX_USER_LOGIN中按照其身份证号查找的第一条（时间）。
根据以上分析，sql如下：

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)

首先，揪出错误的手机号及对应的身份证：

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

根据身份证号查出登录履历表：

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
进行分组排序：

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

取分组排序后的第一条

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，联合原表和表t，得出所有需要的字段：

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 意思就变了,此时就等价于: select * from a inner join b on a.id = b.id。即就是内连接。

后记

分组中用到PARTITION，它还有分区表的意思。接下来学习分区表。