大数据SQL语句

2019独角兽企业重金招聘Python工程师标准>>>

select *   from (select t1.id, t1.cust_stream, t1.srv_stream,    t1.call_time,t1.flow_sn,t1.approve_stat,t1.approve_date,t1.reject_reason,t1.srv_id,
t1.srv_name,t1.cust_id,t1.cust_name,t1.user_id,t1.channel_id,    t2.userid,t2.idno,t2.mobileno,t2.cuacct_code,t2.user_code   
from t_accepted_witnessvideo t1 left join t_accepted_customer_info t2 on t1.cust_id = t2.id    where t1.flow_sn is not null             
and (REVERSE(t1.cust_stream) not like REVERSE('%flv') and REVERSE(t1.cust_stream) not like REVERSE('%mp4')     
and REVERSE(t1.cust_stream) not like REVERSE('%dat'))         
and (REVERSE(t1.channel_id) like REVERSE('%kh') AND t1.channel_id NOT IN ('jybdkh', 'jgjbdkh') or t1.channel_id is null)                                        
and          t1.call_time >= '2018-07-31 17:09:55'
and          t1.call_time <= '2018-10-31 17:09:55'
and         t1.cust_name='王赛'
order by t1.channel_id desc, t1.call_time desc) t 
where 10000 > ROWNUM
AND t.id NOT IN (

select t.id  from (SELECT t1.id, t1.cust_stream, t1.srv_stream,    
t1.call_time,t1.flow_sn,t1.approve_stat,t1.approve_date,t1.reject_reason,t1.srv_id,t1.srv_name,
t1.cust_id,t1.cust_name,t1.user_id,t1.channel_id,    t2.userid,t2.idno,t2.mobileno,t2.cuacct_code,t2.user_code   
from t_accepted_witnessvideo t1 left join t_accepted_customer_info t2 on t1.cust_id = t2.id    
where t1.flow_sn is not null             
and (REVERSE(t1.cust_stream) not like REVERSE('%flv') and REVERSE(t1.cust_stream) not like REVERSE('%mp4') and REVERSE(t1.cust_stream) not like REVERSE('%dat'))         
and (REVERSE(t1.channel_id) like REVERSE('%kh') or t1.channel_id is null)                                           
and (t1.approve_stat = '0' or t1.approve_stat is null)                    
and          t1.call_time >= '2018-07-31 17:09:55'
and          t1.call_time <= '2018-10-31 17:09:55'
and         t1.cust_name='王赛'
order by t1.channel_id desc, t1.call_time desc) t where 10000 > rownum 

UNION

select t.id  from (SELECT t1.id, t1.cust_stream, t1.srv_stream,    t1.call_time,t1.flow_sn,t1.approve_stat,t1.approve_date,
t1.reject_reason,t1.srv_id,t1.srv_name,t1.cust_id,t1.cust_name,t1.user_id,t1.channel_id,    t2.userid,t2.idno,t2.mobileno
,t2.cuacct_code,t2.user_code   
from t_accepted_witnessvideo t1 left join t_accepted_customer_info t2 on t1.cust_id = t2.id    
where t1.flow_sn is not null             
and (REVERSE(t1.cust_stream) not like REVERSE('%flv') and REVERSE(t1.cust_stream) not like REVERSE('%mp4')     and REVERSE(t1.cust_stream) not like REVERSE('%dat'))         
and (REVERSE(t1.channel_id) like REVERSE('%kh') or t1.channel_id is null)                                            
and t1.approve_stat = '1'
and          t1.call_time >= '2018-07-31 17:09:55'
and          t1.call_time <= '2018-10-31 17:09:55'
and         t1.cust_name='王赛'
order by t1.channel_id desc, t1.call_time desc) t where 10000 > rownum 

UNION
 
select t.id  from (SELECT t1.id, t1.cust_stream, t1.srv_stream,    t1.call_time,t1.flow_sn,t1.approve_stat,t1.approve_date,t1.reject_reason,
 t1.srv_id,t1.srv_name,t1.cust_id,t1.cust_name,t1.user_id,t1.channel_id,    t2.userid,t2.idno,t2.mobileno,t2.cuacct_code,
 t2.user_code   from t_accepted_witnessvideo t1 left join t_accepted_customer_info t2 on t1.cust_id = t2.id    
 where t1.flow_sn is not null             
 and (REVERSE(t1.cust_stream) not like REVERSE('%flv') and REVERSE(t1.cust_stream) not like REVERSE('%mp4')     
 and REVERSE(t1.cust_stream) not like REVERSE('%dat'))         
 and (REVERSE(t1.channel_id) like REVERSE('%kh') or t1.channel_id is null)                                      
 and t1.reject_reason is not null     and t1.approve_stat < '4'           
 and          t1.call_time >= '2018-07-31 17:09:55'
 and          t1.call_time <= '2018-10-31 17:09:55'
 and         t1.cust_name='王赛'
 order by t1.channel_id desc, t1.call_time desc) t where 10000 > ROWNUM
 
 UNION
 
 select t.id  from (SELECT t1.id, t1.cust_stream, t1.srv_stream,    t1.call_time,t1.flow_sn,t1.approve_stat,t1.approve_date,
t1.reject_reason,t1.srv_id,t1.srv_name,t1.cust_id,t1.cust_name,t1.user_id,t1.channel_id,    t2.userid,t2.idno,t2.mobileno,
t2.cuacct_code,t2.user_code   
from t_accepted_witnessvideo t1 left join t_accepted_customer_info t2 on t1.cust_id = t2.id    
where t1.flow_sn is not null             
and (REVERSE(t1.cust_stream) not like REVERSE('%flv') and REVERSE(t1.cust_stream) not like REVERSE('%mp4')     
and REVERSE(t1.cust_stream) not like REVERSE('%dat'))         
and (REVERSE(t1.channel_id) like REVERSE('%kh') or t1.channel_id is null)                                            
and t1.approve_stat = '2'
and          t1.call_time >= '2018-07-31 17:09:55'
and          t1.call_time <= '2018-10-31 17:09:55'
and         t1.cust_name='王赛'
order by t1.channel_id desc, t1.call_time desc) t where 10000 > ROWNUM

UNION 

SELECT t.id  from (SELECT t1.id, t1.cust_stream, t1.srv_stream,    t1.call_time,t1.flow_sn,t1.approve_stat,t1.approve_date,
t1.reject_reason,t1.srv_id,t1.srv_name,t1.cust_id,t1.cust_name,t1.user_id,t1.channel_id,    t2.userid,t2.idno,t2.mobileno,
t2.cuacct_code,t2.user_code   from t_accepted_witnessvideo t1 left join t_accepted_customer_info t2 on t1.cust_id = t2.id    
where t1.flow_sn is not null             
and (REVERSE(t1.cust_stream) not like REVERSE('%flv') and REVERSE(t1.cust_stream) not like REVERSE('%mp4')     and REVERSE(t1.cust_stream) not like REVERSE('%dat'))         
and (REVERSE(t1.channel_id) like REVERSE('%kh') or t1.channel_id is null)                                            
and t1.approve_stat = '4'
and          t1.call_time >= '2018-07-31 17:32:05'
and          t1.call_time <= '2018-10-31 17:32:05'
and         t1.cust_name='王赛'
order by t1.channel_id desc, t1.call_time desc) t where 10000 > rownum  

)
 

转载于:https://my.oschina.net/u/3563297/blog/3053736