sql 统计汇总将null 排在最后

最新推荐文章于 2025-07-03 09:16:20 发布

原创最新推荐文章于 2025-07-03 09:16:20 发布 · 912 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #database #null排序

随笔一记专栏收录该内容

87 篇文章

订阅专栏

本文介绍了一个使用MyBatis进行复杂数据查询的例子，包括多表联查、条件筛选及特殊排序方式等。该查询从设备站点表、得分表及工程项目类型表中选取相关信息，并根据实际得分降序排列，特别处理了NULL值的排序。

    <select id="selectScoreInfo" resultType="java.util.Map"  parameterType="java.util.Map">
        select DZShare.T_Y_SBBHDEVICE_SITE.* ,
        ROUND(DZShare.T_Y_SCORE.actual_score ,2) as actual_score ,
        ROUND(DZShare.T_Y_SCORE.total_score ,2) as  total_score,
        DZShare.T_Y_SCORE.time ,
        DZShare.T_Y_SCORE.id as scoreId ,
        DZShare.T_Y_SCORE.status ,
        DZShare.T_Y_TYPE_ENGINEERING_D.engineering_name ,
        (select DZShare.T_Y_SCORE.total_score - sum( DZShare.T_Y_SCORE_DETAILS.score) from
        DZShare.T_Y_SCORE_DETAILS where  DZShare.T_Y_SCORE_DETAILS.score_id = DZShare.T_Y_SCORE.id)
        as originalScore
        from DZShare.T_Y_SBBHDEVICE_SITE , DZShare.T_Y_SCORE , DZShare.T_Y_TYPE_ENGINEERING_D
        where 1=1
        and DZShare.T_Y_SBBHDEVICE_SITE.id = DZShare.T_Y_SCORE.sbbhdevice_id
        and DZShare.T_Y_TYPE_ENGINEERING_D.id = DZShare.T_Y_SBBHDEVICE_SITE.engineering_id
        <if test="country != null and country != '' ">
            and  DZShare.T_Y_SBBHDEVICE_SITE.country in (
            <foreach collection="country" index="index" item="item"  separator="," >
                #{item}
            </foreach>
            )
        </if>
        <if test="workSiteName != null and workSiteName != '' ">
            and DZShare.T_Y_SBBHDEVICE_SITE.work_site_name like '%${workSiteName}%'
        </if>
        <if test="time != null and time != '' ">
            and DZShare.T_Y_SCORE.time = '${time}'
        </if>
        <if test="engineering_code != null and engineering_code != '' ">
            and DZShare.T_Y_SBBHDEVICE_SITE.engineering_id in (
            select id from DZShare.T_Y_TYPE_ENGINEERING_D where
            DZShare.T_Y_TYPE_ENGINEERING_D.engineering_code in (
            <foreach collection="engineering_code" index="index" item="item" separator=",">
                #{item}
            </foreach>
            )
            )
        </if>
        <if test="status != null">
            and DZShare.T_Y_SCORE.status = #{status}
        </if>
        order by DZShare.T_Y_SCORE.actual_score desc nulls last
    </select>