
最近正在做一个显示消息的列表页,列表页中需要根据一个字段来分组显示。并且需要一个分页的效果。
大家也知道group by 后的数据是每一组一行记录,统计分组后的总的记录数又不能用count,所以SQL_CALC_FOUND_ROWS就派上用场了。
所谓SQL_CALC_FOUND_ROWS是指在执行带LIMIT的查询时,附带统计一下如果不加LIMIT的话将会输出多少条结果。
用法:
SELECT SQL_CALC_FOUND_ROWS * FROM `TA` LEFT JOIN `TB` on TA.id = TB.a_id GROUP BY TA.id ORDER BY ctime desc limit 0,20;
查询完毕后再执行:
SELECT FOUND_ROWS();
这个语句返回group by 后的总记录数,如下图:
aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAKgAAABICAIAAADtbSlhAAAFSUlEQVR4nO2ay47aSBRA70fFlsW/RAID/SFZBJoWiE+YVVYWMkLub8gOtRo0yLCdZJa0PcFkYtNZ1MNlbMDj9qOm6x5dKXY9r+uY9OvCK8OyLH693+/3+71lWX+n+fLxw6evwr9CB3z8Et9+/fQBkoi9/xnLsrLzETdM7HB+z1vP8i6WzKuUZBxRQkt8A+Icfn1NfEPcFl8j/yfxF7gh/rs0EPFNZ0GRWXzOR7gh/ps0EPFNZ0GRWXzOR0DxRXj/4v+SBiK+6SwoMovP+QjZ4i3kFk1ovU3+/C+KbyJtpD5QvKKgeEW5Lf4P5D2SS3x4i91uF51ef/yLUV9Ep9fdblftJz6v+J8Y9YVE4v/5iVFfSCX+hFFboHhFA8UrGhKJ948n/+gOdaH2Qp88HU/+8eSvJ1rcagzXJ5+0H51O4vbkrycanUWWSvbO+5Do5fRnR2FYIi6kdDWxWReg6/BhTw+GOFG4dTpscmd+SozvOrMuaA/uhazeGlKJj8gpd+aRfxRi3gcwhmt2u55owMcQ8cJgKp4t1SWmhaXE3rm4RWpfGomRzOitxObivvwVjBfRHlxina3sDvX+LOMRkk9XXsgvXjwd0RM5plvi5+5QJ6d8VTzVxo7+snh/3mfirya2nmhxYk4H+p2u6NgYrsVUEzHrZm73jsUHkR+4Qx06duQHLOw+6JOnQGgJIj9wOmAMV+IFi9VEo+PZUquJBmzNeLXURhkt6XZ3qIM2cnMkJsyy+2A6TyMDTIdmCP0ZHZze0R3qwhPFj1NyyCieo43cC+fLjzWH+CB6GhlAzrqo+PirON/rRmIRNz0z2ftHxtt9+gaQa/IdRuLVuXL7HsV7QeQF7kCHth15AQu7D/pkGQgtdJgxWEVe4LSBXLBYTTQ6XlzKHegApiOsltooXjO9Fx1pmQCmky8xnglvoReWCdrIFWctRwYAWznO/3pWbw2pxIdesBno0LZDL+CxaMNZS+jZPYCelTne7oE+Xqa7VmMNoG1e6E1MPAtxpJjM9cTIgJ61GmtsWcuEtr0Z6MZgld6Fr7Zoxyukb0sL+cWHy5EBIBwWUWiLveJZgzbaZC5FP1iZ4u1eYouL4sPlyODvx/XEqGmzx/IJPbsHZq/NX6/VWDMXgmCyFL/ga75v8dHJO4TegZ3yIRnPY/HH5cFzotcyhW8L7jesPb3UZqAD6OMlv45/OieNmXG2zmagA5iLPIl5dg9AnLtogzD3EC7vDT6ZD7NMYYrdE8eXGFF0kkX8yyHEeDmEL89jTR8vD+HLYfNZNz4/V7KLVOJ/SRCLNiRo2w2ksbw3wFxYJmj3f1a0hUzif/zCqC1kER+i+HojRPFqBopXNOoQv0NkpVrx1yto3/ibBKQw+/2+8Nzc4isr90QKU4f46so9kcLUI76q4i+kMCheUeoTDwAoXh7qEE8qO8lvv8st90RiHu/YXxha022iZzttnTfVJT7yjxH/y0eJVX8I4/GOu328A7h7JNfbaSvzXWhEvOgexVfA4x0VvZ22oDXd8nuRWsQHkR8kxANAKVV/SAbiJ541SCSeuEfxJbOdts60NyieVHaeWS+l3BMR2U5bWV/NmxQfekGYtF5O1R/CSf0Hn+iSQnyJ5Z4IZTttXdLemHhaQUvFl1vuiVDiH+Ip4lvQmHhS2QkApZd7IoWpR3xV5Z5IYWoRf7kGCMU3ReXir9fTvbH4CykMilcUFK8olYvPU+65R5qgWvGFV0ekBcUrCopXFBSvKCheUVC8oqB4RUHxioLiFQXFKwqKVxQUrygoXlFQvKKgeEVB8YqC4hXlNx6OZoBg5CNAAAAAAElFTkSuQmCC" alt="" />
FOUND_ROWS()得到的数字是临时的,执行下一条语句就会失效。所以保存在一个变量中为我们所用就OK了。