个人技术分享

废话不说看代码

<select id="selectByDim" resultType="org.springblade.bigscreen.vo.RightCenterVO">

        SELECT
            dim,
        CAST(avgScore AS SIGNED) AS avgScore
        FROM
            (
                SELECT
                    dsx.batch_id AS batchId,
                    dsx.site_code AS siteCode,
                    dsx.site_name AS site_name,
                    1 AS dim,
                    AVG(JSON_EXTRACT(dsx.statistical, '$."T人格特质"')) AS avgScore
                FROM
                    t_gauge_test AS dsx
                        INNER JOIN t_tester AS dst ON dsx.user_id = dst.id_no
                WHERE
                    dsx.is_deleted = 0
                  AND dst.is_deleted = 0
                  AND JSON_VALID(dsx.statistical)
                GROUP BY
                    dsx.batch_id,
                    dsx.site_name,
                    dsx.site_code

                UNION ALL

                SELECT
                    dsx.batch_id AS batchId,
                    dsx.site_code AS siteCode,
                    dsx.site_name as site_name,
                    2 AS dim,
                    AVG(JSON_EXTRACT(dsx.statistical, '$."T心理健康"')) AS avgScore
                FROM
                    t_gauge_test AS dsx
                        INNER JOIN t_tester AS dst ON dsx.user_id = dst.id_no
                WHERE
                    dsx.is_deleted = 0
                  AND dst.is_deleted = 0
                  AND JSON_VALID(dsx.statistical)
                GROUP BY
                    dsx.batch_id,
                    dsx.site_name,
                    dsx.site_code

                UNION ALL

                SELECT
                    dsx.batch_id AS batchId,
                    dsx.site_code AS siteCode,
                    dsx.site_name as site_name,
                    3 AS dim,
                    AVG(JSON_EXTRACT(dsx.statistical, '$."T压力应对"')) AS avgScore
                FROM
                    t_gauge_test AS dsx
                        INNER JOIN t_tester AS dst ON dsx.user_id = dst.id_no
                WHERE
                    dsx.is_deleted = 0
                  AND dst.is_deleted = 0
                  AND JSON_VALID(dsx.statistical)
                GROUP BY
                    dsx.batch_id,
                    dsx.site_name,
                    dsx.site_code

                UNION ALL

                SELECT
                    dsx.batch_id AS batchId,
                    dsx.site_code AS siteCode,
                    dsx.site_name as site_name,
                    4 AS dim,
                    AVG(JSON_EXTRACT(dsx.statistical, '$."T人际关系"')) AS avgScore
                FROM
                    t_gauge_test AS dsx
                        INNER JOIN t_tester AS dst ON dsx.user_id = dst.id_no
                WHERE
                    dsx.is_deleted = 0
                  AND dst.is_deleted = 0
                  AND JSON_VALID(dsx.statistical)
                GROUP BY
                    dsx.batch_id,
                    dsx.site_name,
                    dsx.site_code

                UNION ALL

                SELECT
                    dsx.batch_id AS batchId,
                    dsx.site_code AS siteCode,
                    dsx.site_name as site_name,
                    5 AS dim,
                    AVG(JSON_EXTRACT(dsx.statistical, '$."T适应能力"')) AS avgScore
                FROM
                    t_gauge_test AS dsx
                        INNER JOIN t_tester AS dst ON dsx.user_id = dst.id_no
                WHERE
                    dsx.is_deleted = 0
                  AND dst.is_deleted = 0
                  AND JSON_VALID(dsx.statistical)
                GROUP BY
                    dsx.batch_id,
                    dsx.site_name,
                    dsx.site_code
            ) AS subquery  where subquery.batchId= #{batchId} and subquery.siteCode in
            <foreach collection="siteCodes" item="siteCode" open="(" separator="," close=")">
                #{siteCode}
            </foreach>;

然后这是上级的mapper

    List<RightCenterVO> selectByDim(Long batchId, List<String> siteCodes);

这个sql包含提取json,子查询，分组，list循环，小数转换成int