很久不用SQL语句了,貌似入职新公司后,又回归到了三年前的SQL时代,一写一坨的SQL好吧,也当回归一下过去的知识。
下面是统计2月份某数据的计费统计
select t.telno as 主号,VID 副号,t.area_code 地区, t.reg_time 注册时间,t.reg_source 注册工号,t.bill_start_date 计费时间,
(case state when 0
then '0 正常' end)计费状态 from t_unreg_log t where t.bill_start_date<to_date('20150301','yyyymmdd') and t.bill_start_date>to_date('20150201','yyyymmdd')
and t.unreg_time<to_date('20150301','yyyymmdd') and t.unreg_time>to_date('20150201','yyyymmdd')
and t.type='9' union
select t.telno as 主号,VID 副号,t.area_code 地区, t.reg_time 注册时间,t.reg_source 注册工号,t.bill_start_date 计费时间,
(case state when 0
then '0 正常' end)计费状态 from t_unreg_log t where t.bill_start_date<to_date('20150301','yyyymmdd') and t.bill_start_date>to_date('20150201','yyyymmdd')
and t.unreg_time>to_date('20150301','yyyymmdd')
union
select t.telno as 主号,VID 副号,t.area_code 地区, t.reg_time 注册时间,t.reg_source 注册工号,t.bill_start_date 计费时间,
(case state when 0
then '0 正常' end)计费状态 from t_unreg_log t where t.bill_start_date is not null
and t.bill_start_date<to_date('20150201','yyyymmdd') and t.unreg_time<to_date('20150301','yyyymmdd') and t.unreg_time>to_date('20150201','yyyymmdd')
and t.type='9'
union
select t.telno as 主号,VID 副号,t.area_code 地区, t.reg_time 注册时间,t.reg_source 注册工号,t.bill_start_date 计费时间,
(case state when 0
then '0 正常' end)计费状态 from t_online_info t where t.bill_start_date is not null and t.bill_start_date<to_date('20150301','yyyymmdd'))
写完之后发现这么多的冗余代码。