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

时间：2025-04-08 20:47:32

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

  • 参考链接
  • 实践
  • 后记

参考链接

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

实践

wx_user的手机号有部分有问题，发生了串登录。原本一个用户对应一个自己的手机号，结果输入其他人的手机号时候，wx_user表中的手机号会更新成其他人的手机号。
比如客户原本存在wx_user的手机号为13088888888，身份证对应为411111111111111111，在页面输入13088888889，在wx_user存的手机号就更新成了13088888889。现在代码修复后，也要将对应的手机号从错误的13088888889改为13088888888。
已知，该用户的初始手机号是在登录履历表WX_USER_LOGIN中按照其身份证号查找的第一条（时间）。

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

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

结果：

411111111111111111

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

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

结果：

ID_NO MOBILE rowss1
411111111111111111 13088888888 1
411111111111111111 13088888889 2

取分组排序后的第一条

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'

结果：

ID_NO MOBILE
411111111111111111 13088888888

接下来，分组排序后的第一条作为表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)

结果：

ID_NO wrongmobile tin truemobile
411111111111111111 13088888889 411111111111111111 13088888888

根据以上分析，最终的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)

后记

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

相关文章