mybatis上为什么不能使用Having?
参考:关于PAGEHELPER无法使用HAVING关键字查询的问题
我开始的代码是这样的
<select id="listCount15" resultType="String">
SELECT
distinct customer_id
FROM
customer_body_metrics
where
DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT(DATE_SUB(now(),interval 1 MONTH),'%Y-%m')
and
metric IN
<foreach collection="metrics" item="value" index="index" open="(" close=")" separator=",">
#{value}
</foreach>
GROUP BY
customer_id,metric
HAVING
count(metric) >= 15
</select>
但是老是报错,说我的
HAVING count(metric) >= 15
有问题,但是我去数据库执行sql语句,又没有问题,经过查资料,我得知,
原来
mybatis会使用一个select cout语句去查询你的sql条件,如果没有数量为0就不会执行你的SQL语句,但是当你的SQL语句使用Having关键字就会报错
修改方案
所以必须重写cout语句,怎么重写呢,在你分页的Mybaits语句id后面新建一个查询,后缀加上_COUNT.
但是SQL语句半天没整出来 现在终于弄好了 作为子表查询数量就可以了.
<select id="listCount15_COUNT" resultType="Integer">
select count(1) from (
SELECT
distinct customer_id
FROM
customer_body_metrics
where
DATE_FORMAT(create_time,'%Y-%m')=DATE_FORMAT(DATE_SUB(now(),interval 1 MONTH),'%Y-%m')
and
metric IN
<foreach collection="metrics" item="value" index="index" open="(" close=")" separator=",">
#{value}
</foreach>
GROUP BY
customer_id,metric
HAVING
count(metric) >= 15
) as t1
</select>