Mysql case when用法

    mysql left join之闭坑指南

    date在where之后，先连表，再筛选，数据此时很多b.date为NULL的数据就被过滤掉了，因此会导致并不是预期的左表的大小

    1. select
    2.     count(*)
    3. from
    4.     dm_data_gr.item_spu as a
    5.     LEFT JOIN dm_data_gr.item_spu_top_sales as b on a.source_spu_id = b.source_spu_id
    6. where
    7.     a.date = ${date}
    8.     and b.date = ${date}
    1. select
    2.     count(*)
    3. from
    4.     dm_data_gr.item_spu as a
    5.     LEFT JOIN dm_data_gr.item_spu_top_sales as b on a.source_spu_id = b.source_spu_id and b.date=${date}
    6. where
    7.     a.date = ${date}
    1. select
    2.     count(*)
    3. from
    4.     (
    5.         select
    6.             *
    7.         from
    8.             dm_data_gr.item_spu
    9.         where
    10.             date = ${date}
    11.     ) as a
    12.     left join (
    13.         select
    14.             *
    15.         from
    16.             dm_data_gr.item_spu_top_sales
    17.         where
    18.             date = ${date}
    19.     ) as b ON a.source_spu_id = b.source_spu_id