子查询存在 group by 时，mybatis 分页异常

原创已于 2024-11-07 11:21:15 修改 · 666 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #java

于 2024-11-07 09:40:37 首次发布

使用mybatis plus 自带的分页时报错

xml 中的sql ：

SELECT
f.id,
CONCAT(f.name,'评价') name,
a.create_time,
'作者' create_user
FROM
( SELECT id, MAX( create_time ) create_time FROM a
<where>
    <if test="params.isAll == 'unall'">id like CONCAT('%',#{params.userId},'%')
    </if>
    </where>
    GROUP BY id ) a
        LEFT JOIN tabB f ON a.id = f.id
<where>
    <if test="params.name!=null and params.name!='' ">
        name like concat('%',#{params.name},'%')
    </if>
</where>
order by a.create_time desc

打印日志：

Cause: java.sql.SQLSyntaxErrorException: Unknown column 'class_name' in 'where clause'
### The error may exist in file [D:\xxx\target\classes\mapper\xx\xx.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT COUNT(*) FROM (SELECT id, MAX(create_time) create_time FROM a GROUP BY id) a WHERE name LIKE concat('%', ?, '%')
### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'name' in 'where clause'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'name' in 'where clause'

我的 name 字段在表 tabB 中，mybatis plus 自带的 select count (*) 却只截取了部分sql。导致 name 加到了子查询结果集中。

解决方案：在最外层套一层，查询sql 成为一个子查询结果集。

select * from (

SELECT f.id, CONCAT(f.name,'评价') name, a.create_time, '作者' create_user FROM ( SELECT id, MAX( create_time ) create_time FROM a <where> <if test="params.isAll == 'unall'">id like CONCAT('%',#{params.userId},'%') </if> </where> GROUP BY id ) a LEFT JOIN tabB f ON a.id = f.id <where> <if test="params.name!=null and params.name!='' "> name like concat('%',#{params.name},'%') </if> </where> order by a.create_time desc

) tabA