net.sf.jsqlparser.statement.select.SubSelect cannot be cast to net.sf.jsqlparser.schema.Table

记录一下，mybatis-plus 使用时 在 select 需要查询的字段中使用变量和函数，出现的异常。如果你也是这种场景出现的问题，可以参考我的解决方案

net.sf.jsqlparser.statement.select.SubSelect cannot be cast to net.sf.jsqlparser.schema.Table

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.ClassCastException: net.sf.jsqlparser.statement.select.SubSelect cannot be cast to net.sf.jsqlparser.schema.Table
### The error may exist in file [D:\java\target\classes\mapping\DeviceMapper.xml]
### The error may involve com.parcel.mapper.DeviceMapper.findPage_mpCount
### The error occurred while executing a query
### Cause: java.lang.ClassCastException: net.sf.jsqlparser.statement.select.SubSelect cannot be cast to net.sf.jsqlparser.schema.Table

 第一种场景

 代码示例

<select id="findPage" resultType="com.vo.DeviceFindPageVO">
	SELECT
		d.id,
    CASE WHEN 
        d.agent_id = #{agentId}        #### 这里使用变量出错
    THEN
		0 
    ELSE 
        1 
	END sales 
	FROM
		pt_device d
		JOIN pt_device_group dg ON d.group_id = dg.id
		JOIN pt_device_server ds ON d.server_id = ds.id
		LEFT JOIN sys_user u1 ON d.agent_id = u1.id AND u1.type = 2
		<if test="agentId !=null and agentId != ''">
			and u1.parent_id = #{agentId}
		</if>
		LEFT JOIN sys_user u2 ON d.user_id = u2.id AND u2.type = 4
		${ew.customSqlSegment}
</select>

解决方法

// xml
<select id="findPage" resultType="com.vo.DeviceFindPageVO">
	SELECT
		d.id
        ${ew.sqlSelect}            #### 使用 mybatisplus的常量拼接符
	FROM
		pt_device d
		JOIN pt_device_group dg ON d.group_id = dg.id
		JOIN pt_device_server ds ON d.server_id = ds.id
		LEFT JOIN sys_user u1 ON d.agent_id = u1.id AND u1.type = 2
		<if test="agentId !=null and agentId != ''">
			and u1.parent_id = #{agentId}
		</if>
		LEFT JOIN sys_user u2 ON d.user_id = u2.id AND u2.type = 4
		${ew.customSqlSegment}
</select>

// java
queryWrapper.select(", case when d.agent_id = " + agentId + " then 0 else 1 end sales");

第二种场景

使用count，查询数据条数的时候，SQL语句中使用函数，例如：DISTINCT、GROUP BY、LEFT等等，也会报错。

示例：

// 你的sql语句
select id from table group by type;
// mp分页的时候，mp会查询数据总数，page<pageNum,pageSize,默认查询总数>
// 会这样查询，然后这句话就会报错，说不能使用函数
select count(*) from (select id from table group by type) as total;

这个时候真的是没办法了，真的是mp的bug，mp官网的github上一大堆bug

解决方法：

使用视图 view ，然后查询这个视图的数据

CREATE OR REPLACE VIEW 视图名称 (字段) AS select id from table group by type;