MySQL 查询分页数据中分组后取每组的前N条记录

在使用数据库查询的时候，如果遇到对分页的数据分组，取每组的前N条，实际就是两次分页，先分页，在对分组的每组排序分页。SQL 如下

<code class="language-SQL hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> a.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> t1.*,(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> <span class="hljs-aggregate" style="box-sizing: border-box;">count</span>(*)+<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> 表 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> 分组字段=t1.分组字段 <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span> 排序字段<t1.排序字段) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> group_id<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> 表 t1) a<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> a.group_id<=<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li></ul>

实例，文章和评论，查询出前2篇文章和对应每篇文章的前2条评论

<code class="language-SQL hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SELECT</span> a.*,c.content <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> c_content<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FROM</span> (<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> ax.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> t_article ax <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ORDER</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> id <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">desc</span> limit <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>,<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">2</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> a<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">LEFT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">JOIN</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> c1.* <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> ( <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> t1.*,(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">select</span> <span class="hljs-aggregate" style="box-sizing: border-box;">count</span>(<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>)+<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> t_comment <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> article_id=t1.article_id <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">and</span> id>t1.id) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> group_id <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">from</span> t_comment t1 ) c1<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> group_id<<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">3</span><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">order</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">by</span> c1.group_id <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">desc</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">as</span> c <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> a.id = c.article_id<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">WHERE</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> = <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ORDER</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">BY</span> a.id <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">DESC</span></span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li></ul>

a – article ,c–comment