【hivesql】找出正常日期

最新推荐文章于 2023-05-07 12:48:28 发布

不爱研究的研究僧

最新推荐文章于 2023-05-07 12:48:28 发布

阅读量568

点赞数

分类专栏：日常总结文章标签： sql hive

本文链接：https://blog.csdn.net/weixin_43955488/article/details/125907260

版权

日常总结专栏收录该内容

8 篇文章 0 订阅

订阅专栏

select出正常的birthday及其它字段，异常的birthdayselect时 id_number、birthday 输出为空，不是更改

1.union分情况

select accountname, '' as birthday, dt, gender, '' as id_number, modify_time
from xsj_acc_real_identity_en
where dt = '2022-07-10'
and birthday not rlike '([1][9]|[2][0])([0-9]{2})-([1-9]|(0[1-9])|1[0-2])-([1-9]|(0[1-9])|([1-2][0-9])|30|31)$'
union all
select *
from xsj_acc_real_identity_en
where dt = '2022-07-10'
and birthday rlike '([1][9]|[2][0])([0-9]{2})-([1-9]|(0[1-9])|1[0-2])-([1-9]|(0[1-9])|([1-2][0-9])|30|31)$'

2.case when

select accountname, dt, gender, modify_time,
case when birthday rlike '([1][9]|[2][0])([0-9]{2})-([1-9]|(0[1-9])|1[0-2])-([1-9]|(0[1-9])|([1-2][0-9])|30|31)$' then birthday
  else '' end as birthday,
case when birthday rlike '([1][9]|[2][0])([0-9]{2})-([1-9]|(0[1-9])|1[0-2])-([1-9]|(0[1-9])|([1-2][0-9])|30|31)$' then id_number
  else '' end as id_number
from xsj_acc_real_identity_en
where dt = '2022-07-10'