msyql

--join函数具体的使用方法，对应的数据表，必须在同一个数据库中
SELECT vt.called_no,cr.check_total_label_list,cr.check_label_list,vt.voice_id,cr.partition_dt
FROM ailab_vqc.check_result_201908 as  cr 
JOIN  ailab_vqc.voice_tab_201908  as vt ON cr.voice_id=vt.voice_id
WHERE cr.check_status=0  AND vt.biz_model_type=4 and  cr.partition_dt="2019-08-30" 
--count(*)通计数据
SELECT count(*)
FROM ailab_vqc.check_result_201908 as  cr 
JOIN  ailab_vqc.voice_tab_201908  as vt ON cr.voice_id=vt.voice_id
WHERE   vt.biz_model_type= 4  and cr.partition_dt="2019-08-26" 

--where子式的写法
SELECT vt.called_no,cr.ai_total_label_list,cr.request_id,cr.ai_label_list,cr.partition_dt
FROM ailab_vqc.check_result_201908 as  cr 
JOIN  ailab_vqc.voice_tab_201908  as vt ON cr.voice_id=vt.voice_id
WHERE vt.bsp_org_id in (‘20150708121351745f91d3‘,
‘20150708130651747bc096‘,
‘2015070813072616d94b3b‘,
‘201507081307534748251d‘,
‘201507081308211d070e61‘,
‘201708022142001743c598‘,
‘201708022142002a8c7aa2‘,
‘20171102214200194dd79b‘,
‘2018040321420036ce1c24‘,
‘2018070321520016ff6c6c‘,
‘20180802215200062440d2‘)   and vt.call_time_length > 60 AND  cr.partition_dt="2019-08-27" 

--between 子式的写法

select result.request_id, sample.editor_sentence_label_list,result.ai_sentence_label_list,sample.editor_asr_fix
from
ailab_vqc.check_result_sample sample
join ailab_vqc.check_result_201909 result
on sample.request_id = result.request_id
and sample.paritition_dt=‘20190902‘ and result.partition_dt="20190902" and result.check_status=0 and sample_time BETWEEN "2019-09-05 08:00:00.0" and "2019-09-05 18:00:00.0"

--各种嵌套

insert OVERWRITE table hdp_teu_dia_guessulike.ai_voice_dm_phone_opp partition(partition_dt = "20190322")
select rt_called_no, regexp_replace(substr(order_date, 1, 10), "-", "") as order_date, opp_id, product_line from (
select call_no_table.rt_called_no, case when oi.earliest_order_date is NULL then "2019-06-30" else oi.earliest_order_date end order_date, oi.opp_id, product_line
from (select rt_called_no, rt_product_line from hdp_mis_dd_defaultdb.app_a_realtime_call_detail35_dy2mv where partition_dt = "20190322" and rt_call_time_length > 0 and rt_call_type = "dialout" and rt_product_line in (55,56) group by rt_called_no, rt_product_line) call_no_table
LEFT Join hdp_teu_dia_guessulike.ai_voice_dm_opp_info oi on call_no_table.rt_called_no = oi.phone and oi.partition_dt = "20190626" and earliest_order_date > "2019-03-22" and call_no_table.rt_product_line = oi.product_line) ai_voice_dm_phone_opp_raw