526互联

记jdbcTemplate使用的一个坑

发布时间 2023-07-20 09:52:49作者: Mack.Meng
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));

仅记录一下，不做深入探究了。