1、在使用jdbcTemplate时,语句不能使用 select * ,不然可能就是这样的错误:
Incorrect column count: expected 1, actual 6
2、如果像这样的外层嵌套,应该去掉外层 select *,
语句:
select * from (
select mater_score.mater_no as materNo,city,town,
mater_score.avg as avgScore,
if(@score = mater_score.avg, @count, @count := @count + 1) cityRank
from (
select mater_no,city,town,
sum(if(`timestamp` = 202303, score, 0)) as `202303`,
sum(if(`timestamp` = 202304, score, 0)) as `202304`,
sum(if(`timestamp` = 202305, score, 0)) as `202305`,
sum(if(`timestamp` = 202306, score, 0)) as `202306`,
avg(score) as `avg`
from t_kpi_fg_pool_mater_star_month
where `timestamp` in (202303,202304,202305,202306) and fk_kpi_id in('GSF12024')
and major='维护'
group by mater_no,city,town
order by `avg` desc
) mater_score,
(select @count := 0, @score := null) t
) t
不然虽然加了类型转换,结果也不是你想要的,对象字段会是空的
jdbcTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(MaterViewMonthReportVO.class));
仅记录一下,不做深入探究了。