废话不说看代码
<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