mysql统计与时间查询

IFNULL(?,0)当数据为空时 返回0
sum( CASE WHEN lso.STATUS != 9 THEN 1 ELSE 0 END ) 当符合when中条件时数据加1，否则不加

<select id="selectStatisticPanel" resultMap="statisticPanelVo" parameterType="com.liuniu.system.domain.vo.article.SearchStatisticDto">
    SELECT
    IFNULL(sum( CASE WHEN lso.STATUS != 9 THEN 1 ELSE 0 END ), 0) AS orderNum,
    IFNULL(sum( CASE WHEN lso.STATUS = 5 THEN 1 ELSE 0 END ), 0) AS finishNum
    FROM
    ln_service_order lso
    <where>
        <if test="period != null and period !=''">
            <choose>
                <when test="period == '近三天'">
                    AND DATE(lso.create_time) >= DATE_SUB(NOW(), INTERVAL 3 DAY)
                </when>
                <when test="period == '近一周'">
                    AND DATE(lso.create_time) >= DATE_SUB(NOW(), INTERVAL 1 WEEK)
                </when>
                <when test="period == '近一月'">
                    AND DATE(lso.create_time) >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
                </when>
                <when test="period == '近一年'">
                    AND DATE(lso.create_time) >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
                </when>
                <otherwise>
                    AND 1=1
                </otherwise>
            </choose>
        </if>

    </where>
</select>

阅读量

赏

谢谢你请我吃糖果

支付宝

微信