- 取a表和b表join的数据sql
原本意图是取a表的昨日分区数据和b表的昨日分区数据进行join,但是发现加上and b.dt = '${daily}'的条件后就取不到在a表中存在的数据了
select substr(a.create_time,1,7) create_month,a.service_id,a.unit_number,a.company_name,coalesce(b.unified_social_credit_code,'unknown') unified_social_credit_code
,a.company_id,b.company_id as company_id_b,a.dt,b.dt
from clouduser.dz_company a
left join clouduser.dz_company_detail b on a.company_id = b.company_id
where a.dt = '${daily}'
--and b.dt = '${daily}'
and substr(a.create_time,1,7) = '2022-09'
and a.company_name = '浙江港都电子有限公司';
- 运行结果
- 原因是where执行是在join之后,join出来的临时表中,由于在b表中没有匹配到数据,所以b.dt为null,这时候执行where b.dt = '${daily}'的条件筛选不到这条数据。
- 优化方法
现在子查询中将b表的昨日分区查询出来,再和a表join
with
r1 as (select * from clouduser.dz_company_detail where dt = '2022-10-17'),
r2 as (
select substr(a.create_time,1,7) create_month,a.service_id,a.unit_number,a.company_name,coalesce(b.unified_social_credit_code,'unknown') unified_social_credit_code
,a.company_id,b.company_id as company_id_b
from clouduser.dz_company a
left join r1 b on a.company_id = b.company_id
where a.dt = '${daily}'
--and b.dt = '${daily}'
and substr(a.create_time,1,7) = '2022-09'
and a.company_name = '浙江港都电子有限公司'
)
select * from r2;
- 执行结果
可以看到正常查询出a表中的数据了