SQL提问！求帮助，急！

Question

• 需要按照不同count的数量（三个 四个或五个+）来展示结果

• 每个count的数量有对应的其他数值（a,b,c,d)

• 需要在特定时间段内找出最高的数值(max abcd)

• 求私！

Answer 1

<select id="queryEventQuantity" resultType="com.jdl.lomir.chint.domain.entity.LomirChintEventInfoQuantity">
        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
      
    </select>