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}'