sharding-jdbc 4 + pagehelper + group by 分表异常

分表 + pagehelper + group by 语句的问题

PageHelper为了算总记录条数，会改写原始sql，做1次count，比如：

select order_id, order_name, order_date from t_order_logic_0 where order_date='2020-09-06';

会首先被改写为：

select count(0) from t_order_logic_0 where order_date='2020-09-06';

然后再由sharding-jdbc改写成:(精确路由)

select count(0) from t_order_0 where order_date='2020-09-06';

对于简单语句，这样没什么问题。但是如果原始语句上，有一些聚合函数或group by，比如下面这样：

　如上图，加了group by 后，下面的语句

SELECT count(0) FROM (SELECT order_id FROM t_order_logic WHERE order_date = ? GROUP BY order_id) table_count

sharding-jdbc并不能正确解析为t_order_0，仍然还是t_order_logic

SELECT count(0) FROM (SELECT order_id FROM t_order_logic WHERE order_date = ? GROUP BY order_id) table_count ::: [2020-09-06]

解决办法：pagehelper对于count语句，允许用户自定义，只要在原来的语句id，加上“_COUNT”

<select id="selectList2" resultMap="BaseResultMap">
select order_id from t_order_logic${tableIndex} where order_date=#{orderDate} group by order_id
</select>

<select id="selectList2_COUNT" resultType="long">
select count(1) from t_order_logic${tableIndex} where order_date=#{orderDate} group by order_id
</select>