SQL Notes | BiliBili Internship - 左连接信息补全
需求：给订单表加上代理商与品牌这4列信息
SELECT
t1.crm_order_id,
t3.account_id,
t3.account_name
FROM
tmp_sycpb.chenmingyang01_20250523_173207 t1
LEFT JOIN bili_sycpb.dwd_trad_cord_crm_order_a_d t4 ON t1.crm_order_id = t4.id
LEFT JOIN bili_sycpb.dwd_trad_cntc_crm_contract_a_d t2 ON t4.crm_contract_id = t2.id
LEFT JOIN bili_sycpb.dim_bdata_common_ad_account_brand_d t3 ON t2.account_id = t3.account_id
WHERE
t4.log_date = '${yyyyMMdd}'
AND t2.log_date = '${yyyyMMdd}'
AND t3.log_date = '${yyyyMMdd}'
SELECT
t1.*,
t3.customer_id,
t3.customer_name,
t3.account_id,
t3.account_name
FROM
tmp_sycpb.chenmingyang01_20250523_171844 t1
LEFT JOIN bi_sycpb.ads_spark_trade_order_detail_info_a_d t2
ON t1.order_id = t2.order_id
LEFT JOIN bili_sycpb.dim_bdata_common_ad_account_brand_d t3
ON t2.account_id = t3.account_id
WHERE
t2.log_date = '${yyyyMMdd}'
AND t3.log_date = '${yyyyMMdd}'
腾讯云智研发成长空间 117人发布
投递腾讯云智研发等公司8个岗位 >
