Case when 与 left join 的区别

CodeDunkster

于 2023-09-20 12:54:55 发布

阅读量521

点赞数

文章标签：数据库开发语言 java

本文链接：https://blog.csdn.net/weixin_54574094/article/details/133072048

版权

文章讲述了在SQL查询中，如何使用`Casewhen`替代复杂的`leftjoin`条件，以更优雅地处理两张表之间的数据关联，特别是在过滤条件中排除特定类别的情况。

摘要由CSDN通过智能技术生成

Case when 与 left join 的区别

left join

下面 left join 的两张表基本相同，就是第一张表多了一个过滤条件 and yt.yt_name not regexp ‘储藏室|非机动’，可以用 case when 优雅的解决。

    <select id="volumeTrendChar1" resultType="com.longfor.c4cockpit.pojo.vo.board.ParkingIndexCharVo">
        select
        yearKey,
        date_trunc('month',snapshot_date) as monthKey,
        quantityMonthActual AS monthActual,
        Round( valueMonthActual / quantityMonthActual, 1 ) AS averagePrice
        From(
        SELECT t1.yearKey,t1.snapshot_date,t1.quantityMonthActual,t2.valueMonthActual from(
        SELECT
        snapshot_date,
        YEAR(snapshot_date) as yearKey,
        sum( CASE WHEN date_key >= date_trunc ( 'month', snapshot_date ) AND date_key &lt;= snapshot_date THEN qyts ELSE 0 END ) AS quantityMonthActual
        FROM
        app.a03_sale_report_base xsrb
        LEFT JOIN dim.d18_c12c4_mapping rel ON rel.crm_phase_code = xsrb.ph_id
        INNER JOIN dim.d18_mdm_project pr ON rel.pms_project_card = pr.project_card
        INNER JOIN dim.d03_yt_type yt ON xsrb.yt_code = yt.yt_code
        WHERE
        snapshot_date in
        <foreach collection="list" item="item_date" separator="," open="(" close=")">
            #{item_date}
        </foreach>
        and yt.yt_name not regexp '储藏室|非机动'
        and yt.yt_category = '车位'
        and date_key >= date_trunc('year', snapshot_date) and date_key &lt;= snapshot_date
        and pr.project_id = #{req.authCode}
        group by snapshot_date
        )as t1
        left join
        (select
        snapshot_date,
        sum(case when date_key >= date_trunc('month', snapshot_date) and date_key &lt;= snapshot_date then qyzj else 0 end) as valueMonthActual -- 货值 月度实际
        from app.a03_sale_report_base xsrb
        left join dim.d18_c12c4_mapping rel on rel.crm_phase_code = xsrb.ph_id
        inner join dim.d18_mdm_project pr on rel.pms_project_card = pr.project_card
        inner join dim.d03_yt_type yt on xsrb.yt_code = yt.yt_code
        where snapshot_date in
        <foreach collection="list" item="item_date" separator="," open="(" close=")">
            #{item_date}
        </foreach>
        and yt.yt_category = '车位'
        and date_key >= date_trunc('year', snapshot_date) and date_key &lt;= snapshot_date
        and pr.project_id = #{req.authCode}
        group by snapshot_date)as t2
        ON t1.snapshot_date=t2.snapshot_date
        )as t
        ORDER BY monthKey
    </select>

Case when优雅解决

    <select id="volumeTrendChar" resultType="com.longfor.c4cockpit.pojo.vo.board.ParkingIndexCharVo">
       select
       yearKey,
       date_trunc('month',snapshot_date) as monthKey,
       quantityMonthActual AS monthActual,
       Round( valueMonthActual / quantityMonthActual, 1 ) AS averagePrice
       From(
       SELECT
       snapshot_date,
       YEAR(snapshot_date) as yearKey,
       sum( CASE WHEN date_key >= date_trunc ( 'month', snapshot_date ) AND date_key &lt;= snapshot_date AND yt.yt_name not regexp '储藏室|非机动' THEN qyts ELSE 0 END ) AS quantityMonthActual,
       sum(case when date_key >= date_trunc('month', snapshot_date) and date_key &lt;= snapshot_date then qyzj else 0 end) as valueMonthActual -- 货值 月度实际
       FROM
       app.a03_sale_report_base xsrb
       LEFT JOIN dim.d18_c12c4_mapping rel ON rel.crm_phase_code = xsrb.ph_id
       INNER JOIN dim.d18_mdm_project pr ON rel.pms_project_card = pr.project_card
       INNER JOIN dim.d03_yt_type yt ON xsrb.yt_code = yt.yt_code
       WHERE
       snapshot_date in
       <foreach collection="list" item="item_date" separator="," open="(" close=")">
           #{item_date}
       </foreach>
       and yt.yt_category = '车位'
       and date_key >= date_trunc('year', snapshot_date) and date_key &lt;= snapshot_date
       and pr.project_id = #{req.authCode}
       group by snapshot_date
       )as t
       ORDER BY monthKey
   </select>