mysql获取group by的总记录行数方法子查询 亲测

mysql获取group by的总记录行数方法 原方法： 1. group by 后分组的前十条，在页面展示：size=20

SELECT column_name as count FROM mac_activity ma left join mac_ticket_channel mtc on ma.id=mtc.activity_id where ma.delete_flag=0 and mtc.delete_flag=0 group by mtc.activity_id,mtc.issue_channel_cd order by ma.create_time desc limit #{pageBean.offset},#{pageBean.size} ;

2. group by的总记录数，也需要在页面显示：

select count(*) from (SELECT count(distinct ma.id) as count FROM mac_activity ma left join mac_ticket_channel mtc on ma.id=mtc.activity_id group by mtc.activity_id) T;

原方法已亲测，group by mtc.activity_id,mtc.issue_channel_cd现查询当前页条数的group by有2个分组条件，所以分页查询时每页的条数=每页的条数*2，同理如果是3个分组条件，每页的条数=每页的条数*3.

查询总条数时通过子查询完成，子查询中查id时加上distinct去掉重复的，子查询分组时只用1个条件用活动activity_id即可不需要2个条件，加上issue_channel_cd将线上和线下分别都作为总数加到一块儿了，如果加（）后，记得加临时表名T，否则报错every derived table must have its own alias,如果不给临时表加（）时，报语法错误。

新方法： mysql使用关键字SQL_CALC_FOUND_ROWS获取group by的总记录行数

1.SELECT SQL_CALC_FOUND_ROWS column_id,count(*) as count FROM my_table group by column_id limit 10; 2.select FOUND_ROWS();

 

SQL_CALC_FOUND_ROWS所在的查询语句中，记录查询到的总条数（与limit无关）； select FOUND_ROWS();即返回查询语句中找到的总记录数；