Case when 与 left join 的区别

文章讲述了在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>

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值