Java COUNT(case when)语法

Java与MySQL中的COUNT(CASE WHEN)用法解析

原创已于 2022-08-25 17:26:22 修改 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #mysql

于 2022-03-22 14:08:59 首次发布

本文详细介绍了在Java应用程序中如何利用MySQL的COUNT(CASE WHEN)语句进行复杂条件计数。通过案例分析，展示了如何在查询中使用此语法来统计满足特定条件的数据行数，从而提升数据处理的效率和灵活性。

<select id="queryEventQuantity" resultType="com.entity.Quantity">
        SELECT
        COUNT(*) allQuantity,
        COUNT(case when e.status='CONFIRM' then 1 END ) confirmQuantity,
        COUNT(case when e.status='UNTREATED' then 1 END ) untreatedQuantity,
        COUNT(case when e.status='INVALID' then 1 END ) invalidQuantity,
        <choose>
            <when test="status!=null">
                COUNT(case when e.status=#{status.name} then 1 END ) eventTypeQuantity,
                COUNT(case when e.event_type='COLLISION' and e.status=#{status.name} then 1 END ) collisionQuantity,
                COUNT(case when e.event_type='OVERSPEED' and e.status=#{status.name} then 1 END ) overspeedQuantity,
                COUNT(case when e.event_type='HUMIDITY_EXCEEDED' and e.status=#{status.name} then 1 END ) humidityQuantity,
                COUNT(case when e.event_type='COST' and e.status=#{status.name} then 1 END ) costQuantity,
                COUNT(case when e.event_type='DAMAGE' and e.status=#{status.name} then 1 END ) damageQuantity,
                COUNT(case when e.event_type='DELAY' and e.status=#{status.name} then 1 END ) delayQuantity
            </when>
            <otherwise>
                COUNT(e.event_type) eventTypeQuantity,
                COUNT(case when e.event_type='COLLISION' then 1 END ) collisionQuantity,
                COUNT(case when e.event_type='OVERSPEED' then 1 END ) overspeedQuantity,
                COUNT(case when e.event_type='HUMIDITY_EXCEEDED' then 1 END ) humidityQuantity,
                COUNT(case when e.event_type='COST' then 1 END ) costQuantity,
                COUNT(case when e.event_type='DAMAGE' then 1 END ) damageQuantity,
                COUNT(case when e.event_type='DELAY' then 1 END ) delayQuantity
            </otherwise>
        </choose>
        FROM lomir_chint_event_info e
        <where>
            e.is_delete=0
            <if test="voucherCode!=null and voucherCode!=''">
                <![CDATA[ AND e.voucher_code like  concat('%',#{voucherCode},'%') ]]>
            </if>
            <if test="customerOrderNumber!=null and customerOrderNumber!=''">
                <![CDATA[ AND  e.customer_order_number like  concat('%',#{customerOrderNumber},'%') ]]>
            </if>
            <if test="beginTime!=null">
                <![CDATA[ AND e.event_time >=#{beginTime}]]>
            </if>
            <if test="endTime!=null">
                <![CDATA[ AND e.event_time <=#{endTime}]]>
            </if>
        </where>
    </select>