问题出在分页的位置：一条订单关联多条明细，LIMIT 截取的是关联后的行；页面需要的则是20个完整订单。要修的不只是订单数量，还有每个订单的明细是否齐全。
下面用 MyBatis 3.5.19、H2 2.3.232、JDK 21 对比两种改法：先分页订单 ID，再取明细；或者把订单分页放进子查询。实验没有使用分页插件。
这20行，到底是什么
准备 25 个订单。为了看清边界，前几条数据这样放：
| 订单 | 明细数 | JOIN 后占用的行 |
|---|---|---|
| 1—6 | 每单3条 | 前18行 |
| 7 | 4条 | 第19—22行 |
| 8 | 0条 | LEFT JOIN 仍保留1行 |
| 9—25 | 每单1条 | 后续17行 |
所有订单的 created_at 相同，再按唯一的订单 ID 排序。这样不会靠碰巧不同的时间值得到稳定顺序。
错误查询如下：
<span>SELECT</span> o.id <span>AS</span> order_id, d.id <span>AS</span> line_id, d.sku
<span>FROM</span> orders o
<span>LEFT</span> <span>JOIN</span> details d <span>ON</span> d.order_id <span>=</span> o.id
<span>ORDER</span> <span>BY</span> o.created_at, o.id, d.id
LIMIT <span>20</span>;
实际结果是：
SQL结果：20行
映射后的订单：7个
每单拿到的明细数：[3, 3, 3, 3, 3, 3, 2]
第七个订单占第19—22行，LIMIT 20 只留下前两条明细。订单对象虽然还在，内容已经不完整了。
MyBatis 的嵌套 collection 会把相同订单对应的多行组成一个对象，因此二十行不会自动变成二十个订单。父、子映射都明确写 <id>，正是为了识别对应对象；可对照官方 collection 嵌套结果示例。
这里同时选了明细 ID 和 SKU。给整条查询加 DISTINCT，这些明细行仍然不同，不能把问题变成“只返回二十个完整订单”。
先选订单，再取明细
要让分页单位回到订单，先确定本页有哪些订单，再展开它们的明细。第一种办法分两次查询，先取本页订单 ID：
<span>SELECT</span> o.id
<span>FROM</span> orders o
<span>ORDER</span> <span>BY</span> o.created_at, o.id
LIMIT <span>20</span> <span>OFFSET</span> <span>0</span>;
然后只展开这二十个订单：
<span>SELECT</span> o.id <span>AS</span> order_id, d.id <span>AS</span> line_id, d.sku
<span>FROM</span> orders o
<span>LEFT</span> <span>JOIN</span> details d <span>ON</span> d.order_id <span>=</span> o.id
<span>WHERE</span> o.id <span>IN</span> (<span>/* 第一条查询得到的ID */</span>)
<span>ORDER</span> <span>BY</span> o.created_at, o.id, d.id;
第二条 SQL 不再对展开后的行做 LIMIT。完整程序用 foreach 绑定 ID 参数，返回后再按第一次得到的 ID 顺序组装，避免把 IN 当成排序规则。
空页直接返回，不执行第二条查询。没有明细的订单通过 LEFT JOIN 保留下来，collection 设置 notNullColumn="line_id"，不创建一条全空的明细对象。
实测第一页有 20 个订单，订单 7 得到完整的 4 条明细，订单 8 的明细列表为空；第二页是订单 21—25。
如果希望一条 SQL 完成，也可以把刚才的订单分页放进子查询，再在外层关联明细：
<span>SELECT</span> o.id <span>AS</span> order_id, d.id <span>AS</span> line_id, d.sku
<span>FROM</span> (
<span>SELECT</span> id, created_at
<span>FROM</span> orders
<span>ORDER</span> <span>BY</span> created_at, id
LIMIT <span>20</span> <span>OFFSET</span> <span>0</span>
) o
<span>LEFT</span> <span>JOIN</span> details d <span>ON</span> d.order_id <span>=</span> o.id
<span>ORDER</span> <span>BY</span> o.created_at, o.id, d.id;
这里仍然先分页订单，再展开明细。外层的 ORDER BY 也要保留，不能依赖子查询替最终结果排序。两种写法在这组数据上返回相同的订单、明细和顺序。
选哪种还要看实际查询和数据库执行计划。本例只核对分页结果，没有做 MySQL 性能测试，也不能因为一条 SQL 就宣布它一定更快。
筛订单，还是筛要展示的明细
分页确定了每页取哪些订单，筛选还要确定每单展示哪些明细。比如页面要求“包含 wanted 商品的订单”：找到订单以后，是展示它的全部明细，还是只显示 wanted 那几行？
本例选择前者。订单 1 的三条明细里有一条命中，订单 7 的四条明细里也有一条命中。结果应该返回两个订单，明细数分别是 3、4。
可以在父表分页条件里用 EXISTS：
<span>SELECT</span> o.id
<span>FROM</span> orders o
<span>WHERE</span> <span>EXISTS</span> (
<span>SELECT</span> <span>1</span> <span>FROM</span> details f
<span>WHERE</span> f.order_id <span>=</span> o.id <span>AND</span> f.sku <span>=</span> <span>'wanted'</span>
)
<span>ORDER</span> <span>BY</span> o.created_at, o.id
LIMIT <span>20</span> <span>OFFSET</span> <span>0</span>;
后续获取明细时不再按 SKU 裁掉其他行。订单总数也用同一个 EXISTS 条件，从订单表计数。
如果页面只展示命中的明细，就要在明细查询上加对应条件。这两种需求都合理，但筛选条件放在哪里、最终返回什么，需要先约定清楚。
这份数据里，订单总数是 25，LEFT JOIN 展开的总行数却是 40。总页数要跟页面里的实体一致；拿 40 当订单总数，页码也会出错。
别漏掉第二页和空明细
完整程序检查了这些结果：
| 检查内容 | 本次实际结果 |
|---|---|
| 直接 JOIN 后 LIMIT | 20行 → 7个订单，第7单只剩2条明细 |
| 先分页 ID，第一页 | 20个订单；第7单4条明细，第8单空列表；2次查询 |
| 先分页 ID，第二页 | 21—25，与第一页无重复；2次查询 |
| 超出最后一页 | 空列表；只查1次 |
| 分页子查询 | 前两页与两次查询版的内容、顺序一致 |
| 总数 | 25个订单；不是40条关联结果 |
| 筛包含 wanted 的订单 | 返回1、7，明细仍是3、4条；总数2 |
这里的数据在读取期间没有变化。唯一排序能消除同时间值的排序歧义，不能阻止并发新增、删除让 OFFSET 页码移动。两次查询之间的数据一致性，也取决于数据库、事务和隔离级别，不能因共用 SqlSession 就认为天然得到同一快照。
演示程序发现分页选中的订单随后缺失，会直接报错，方便暴露这个条件；生产接口应按实际业务决定快照、重试或缺失项处理方式。高频变化的大列表还可以另行评估游标分页，本文没有实现它。
复制运行
下面两个文件就是完整程序。项目目录执行：
mvn -q compile <span>exec</span>:java
最终打印 PASS: 10 assertions。这些断言核对结果集合、明细完整性、顺序和查询数，运行不需要业务数据库。
pom.xml：
<span><<span>project</span> <span>xmlns</span>=<span>"http://maven.apache.org/POM/4.0.0"</span>
<span>xmlns:xsi</span>=<span>"http://www.w3.org/2001/XMLSchema-instance"</span>
<span>xsi:schemaLocation</span>=<span>"http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"</span>></span>
<span><<span>modelVersion</span>></span>4.0.0<span></<span>modelVersion</span>></span>
<span><<span>groupId</span>></span>demo<span></<span>groupId</span>></span>
<span><<span>artifactId</span>></span>order-page-lab<span></<span>artifactId</span>></span>
<span><<span>version</span>></span>1.0<span></<span>version</span>></span>
<span><<span>properties</span>></span>
<span><<span>maven.compiler.release</span>></span>21<span></<span>maven.compiler.release</span>></span>
<span><<span>project.build.sourceEncoding</span>></span>UTF-8<span></<span>project.build.sourceEncoding</span>></span>
<span></<span>properties</span>></span>
<span><<span>dependencies</span>></span>
<span><<span>dependency</span>></span>
<span><<span>groupId</span>></span>org.mybatis<span></<span>groupId</span>></span>
<span><<span>artifactId</span>></span>mybatis<span></<span>artifactId</span>></span>
<span><<span>version</span>></span>3.5.19<span></<span>version</span>></span>
<span></<span>dependency</span>></span>
<span><<span>dependency</span>></span>
<span><<span>groupId</span>></span>com.h2database<span></<span>groupId</span>></span>
<span><<span>artifactId</span>></span>h2<span></<span>artifactId</span>></span>
<span><<span>version</span>></span>2.3.232<span></<span>version</span>></span>
<span></<span>dependency</span>></span>
<span></<span>dependencies</span>></span>
<span><<span>build</span>></span>
<span><<span>plugins</span>></span>
<span><<span>plugin</span>></span>
<span><<span>groupId</span>></span>org.apache.maven.plugins<span></<span>groupId</span>></span>
<span><<span>artifactId</span>></span>maven-compiler-plugin<span></<span>artifactId</span>></span>
<span><<span>version</span>></span>3.13.0<span></<span>version</span>></span>
<span></<span>plugin</span>></span>
<span><<span>plugin</span>></span>
<span><<span>groupId</span>></span>org.codehaus.mojo<span></<span>groupId</span>></span>
<span><<span>artifactId</span>></span>exec-maven-plugin<span></<span>artifactId</span>></span>
<span><<span>version</span>></span>3.5.0<span></<span>version</span>></span>
<span><<span>configuration</span>></span>
<span><<span>mainClass</span>></span>demo.PageLab<span></<span>mainClass</span>></span>
<span></<span>configuration</span>></span>
<span></<span>plugin</span>></span>
<span></<span>plugins</span>></span>
<span></<span>build</span>></span>
<span></<span>project</span>></span>
src/main/java/demo/PageLab.java：
<span>package</span> demo;
<span>import</span> java.io.StringReader;
<span>import</span> java.sql.Statement;
<span>import</span> java.util.*;
<span>import</span> org.apache.ibatis.annotations.Param;
<span>import</span> org.apache.ibatis.builder.xml.XMLMapperBuilder;
<span>import</span> org.apache.ibatis.datasource.unpooled.UnpooledDataSource;
<span>import</span> org.apache.ibatis.executor.statement.StatementHandler;
<span>import</span> org.apache.ibatis.mapping.Environment;
<span>import</span> org.apache.ibatis.plugin.*;
<span>import</span> org.apache.ibatis.session.*;
<span>import</span> org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
<span>public</span> <span>class</span> <span>PageLab</span> {
<span>public</span> <span>static</span> <span>class</span> <span>Line</span> { <span>public</span> <span>int</span> id; <span>public</span> String sku; }
<span>public</span> <span>static</span> <span>class</span> <span>Order</span> {
<span>public</span> <span>int</span> id;
<span>public</span> List<Line> lines = <span>new</span> <span>ArrayList</span><>();
}
<span>public</span> <span>interface</span> <span>Mapper</span> {
List<Map<String,Object>> <span>raw</span><span>()</span>;
List<Order> <span>bad</span><span>()</span>;
List<Integer> <span>ids</span><span>(<span>@Param("offset")</span> <span>int</span> offset, <span>@Param("size")</span> <span>int</span> size,
<span>@Param("sku")</span> String sku)</span>;
List<Order> <span>details</span><span>(<span>@Param("ids")</span> List<Integer> ids)</span>;
List<Order> <span>subquery</span><span>(<span>@Param("offset")</span> <span>int</span> offset, <span>@Param("size")</span> <span>int</span> size,
<span>@Param("sku")</span> String sku)</span>;
<span>int</span> <span>count</span><span>(<span>@Param("sku")</span> String sku)</span>;
<span>int</span> <span>joinCount</span><span>()</span>;
}
<span>// XML 放在字符串里，方便复制复现；实际项目可放到 Mapper.xml。</span>
<span>static</span> <span>final</span> <span>String</span> <span>XML</span> <span>=</span> <span>"""
<?xml version="1.0" encoding="UTF-8"?>
<mapper namespace="demo.PageLab$Mapper">
<resultMap id="order" type="demo.PageLab$Order">
<id property="id" column="order_id"/>
<collection property="lines" ofType="demo.PageLab$Line" notNullColumn="line_id">
<id property="id" column="line_id"/>
<result property="sku" column="sku"/>
</collection>
</resultMap>
<sql id="columns">o.id AS order_id,d.id AS line_id,d.sku</sql>
<sql id="eligible">
<if test="sku != null">
WHERE EXISTS (SELECT 1 FROM details f WHERE f.order_id=o.id AND f.sku=#{sku})
</if>
</sql>
<sql id="wrong">
SELECT <include refid="columns"/> FROM orders o
LEFT JOIN details d ON d.order_id=o.id
ORDER BY o.created_at,o.id,d.id LIMIT 20
</sql>
<select id="raw" resultType="map"><include refid="wrong"/></select>
<select id="bad" resultMap="order"><include refid="wrong"/></select>
<select id="ids" resultType="int">
SELECT o.id FROM orders o <include refid="eligible"/>
ORDER BY o.created_at,o.id LIMIT #{size} OFFSET #{offset}
</select>
<select id="details" resultMap="order">
SELECT <include refid="columns"/> FROM orders o
LEFT JOIN details d ON d.order_id=o.id WHERE o.id IN
<foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
ORDER BY o.created_at,o.id,d.id
</select>
<select id="subquery" resultMap="order">
SELECT <include refid="columns"/> FROM
(SELECT o.id,o.created_at FROM orders o <include refid="eligible"/>
ORDER BY o.created_at,o.id LIMIT #{size} OFFSET #{offset}) o
LEFT JOIN details d ON d.order_id=o.id
ORDER BY o.created_at,o.id,d.id
</select>
<select id="count" resultType="int">
SELECT COUNT(*) FROM orders o <include refid="eligible"/>
</select>
<select id="joinCount" resultType="int">
SELECT COUNT(*) FROM orders o LEFT JOIN details d ON d.order_id=o.id
</select>
</mapper>
"""</span>;
<span>@Intercepts(@Signature(type=StatementHandler.class, method="query",
args={Statement.class, ResultHandler.class}))</span>
<span>public</span> <span>static</span> <span>class</span> <span>Counter</span> <span>implements</span> <span>Interceptor</span> {
<span>int</span> count;
<span>public</span> Object <span>intercept</span><span>(Invocation invocation)</span> <span>throws</span> Throwable {
count++;
<span>return</span> invocation.proceed();
}
}
<span>static</span> List<Order> <span>twoQueries</span><span>(Mapper m, <span>int</span> offset, String sku)</span> {
<span>var</span> <span>ids</span> <span>=</span> m.ids(offset, <span>20</span>, sku);
<span>if</span> (ids.isEmpty()) <span>return</span> List.of();
<span>var</span> <span>index</span> <span>=</span> <span>new</span> <span>HashMap</span><Integer,Order>();
m.details(ids).forEach(o -> index.put(o.id, o));
<span>// 以第一次分页的 ID 顺序为准，不依赖 IN 集合的返回顺序。</span>
<span>return</span> ids.stream().map(id -> Objects.requireNonNull(index.get(id))).toList();
}
<span>static</span> List<Integer> <span>ids</span><span>(List<Order> orders)</span> {
<span>return</span> orders.stream().map(o -> o.id).toList();
}
<span>static</span> List<String> <span>snapshot</span><span>(List<Order> orders)</span> {
<span>return</span> orders.stream().map(o -> o.id + <span>":"</span> +
o.lines.stream().map(d -> d.id + <span>"/"</span> + d.sku).toList()).toList();
}
<span>static</span> <span>void</span> <span>require</span><span>(<span>boolean</span> ok, String message)</span> {
<span>if</span> (!ok) <span>throw</span> <span>new</span> <span>AssertionError</span>(message);
}
<span>public</span> <span>static</span> <span>void</span> <span>main</span><span>(String[] args)</span> <span>throws</span> Exception {
<span>var</span> <span>ds</span> <span>=</span> <span>new</span> <span>UnpooledDataSource</span>(<span>"org.h2.Driver"</span>, <span>"jdbc:h2:mem:page_lab"</span>, <span>"sa"</span>, <span>""</span>);
<span>try</span> (<span>var</span> <span>keep</span> <span>=</span> ds.getConnection(); <span>var</span> <span>sql</span> <span>=</span> keep.createStatement()) {
sql.execute(<span>"CREATE TABLE orders(id INT PRIMARY KEY,created_at INT NOT NULL)"</span>);
sql.execute(<span>"CREATE TABLE details(id INT PRIMARY KEY,order_id INT NOT NULL,sku VARCHAR(20))"</span>);
<span>try</span> (<span>var</span> <span>order</span> <span>=</span> keep.prepareStatement(<span>"INSERT INTO orders VALUES(?,1000)"</span>);
<span>var</span> <span>detail</span> <span>=</span> keep.prepareStatement(<span>"INSERT INTO details VALUES(?,?,?)"</span>)) {
<span>for</span> (<span>int</span> <span>id</span> <span>=</span> <span>1</span>; id <= <span>25</span>; id++) {
order.setInt(<span>1</span>,id); order.executeUpdate();
<span>int</span> <span>n</span> <span>=</span> id <= <span>6</span> ? <span>3</span> : id == <span>7</span> ? <span>4</span> : id == <span>8</span> ? <span>0</span> : <span>1</span>;
<span>for</span> (<span>int</span> <span>j</span> <span>=</span> <span>1</span>; j <= n; j++) {
detail.setInt(<span>1</span>,id*<span>10</span>+j); detail.setInt(<span>2</span>,id);
detail.setString(<span>3</span>,(id==<span>1</span> && j==<span>1</span> || id==<span>7</span> && j==<span>3</span>) ? <span>"wanted"</span> : <span>"other"</span>);
detail.executeUpdate();
}
}
}
<span>var</span> <span>config</span> <span>=</span> <span>new</span> <span>Configuration</span>(<span>new</span> <span>Environment</span>(<span>"lab"</span>,<span>new</span> <span>JdbcTransactionFactory</span>(),ds));
<span>var</span> <span>counter</span> <span>=</span> <span>new</span> <span>Counter</span>(); config.addInterceptor(counter);
<span>new</span> <span>XMLMapperBuilder</span>(<span>new</span> <span>StringReader</span>(XML),config,<span>"inline.xml"</span>,config.getSqlFragments()).parse();
<span>var</span> <span>factory</span> <span>=</span> <span>new</span> <span>SqlSessionFactoryBuilder</span>().build(config);
<span>try</span> (<span>var</span> <span>s</span> <span>=</span> factory.openSession()) {
<span>var</span> <span>m</span> <span>=</span> s.getMapper(Mapper.class);
<span>var</span> <span>raw</span> <span>=</span> m.raw(); <span>var</span> <span>bad</span> <span>=</span> m.bad();
require(raw.size()==<span>20</span> && bad.size()==<span>7</span> && bad.get(<span>6</span>).lines.size()==<span>2</span>,<span>"反例"</span>);
System.out.println(<span>"BAD raw=20 orders=7 order7.lines=2"</span>);
System.out.println(<span>"BAD groups="</span>+bad.stream().map(o -> o.id+<span>":"</span>+o.lines.size()).toList());
counter.count=<span>0</span>;
<span>var</span> <span>first</span> <span>=</span> twoQueries(m,<span>0</span>,<span>null</span>);
require(first.size()==<span>20</span> && first.get(<span>6</span>).lines.size()==<span>4</span>
&& first.get(<span>7</span>).id==<span>8</span> && first.get(<span>7</span>).lines.isEmpty() && counter.count==<span>2</span>,<span>"第一页"</span>);
System.out.println(<span>"TWO page1 orders=20 order7.lines=4 order8.lines=0 queries=2"</span>);
counter.count=<span>0</span>;
<span>var</span> <span>second</span> <span>=</span> twoQueries(m,<span>20</span>,<span>null</span>);
require(ids(second).equals(List.of(<span>21</span>,<span>22</span>,<span>23</span>,<span>24</span>,<span>25</span>)) && counter.count==<span>2</span>,<span>"第二页"</span>);
require(Collections.disjoint(ids(first),ids(second)),<span>"页间重复"</span>);
System.out.println(<span>"TWO page2 ids="</span>+ids(second)+<span>" queries=2"</span>);
counter.count=<span>0</span>;
require(twoQueries(m,<span>40</span>,<span>null</span>).isEmpty() && counter.count==<span>1</span>,<span>"空页"</span>);
System.out.println(<span>"TWO empty queries=1"</span>);
counter.count=<span>0</span>;
require(snapshot(m.subquery(<span>0</span>,<span>20</span>,<span>null</span>)).equals(snapshot(first)) && counter.count==<span>1</span>,<span>"子查询第一页"</span>);
require(snapshot(m.subquery(<span>20</span>,<span>20</span>,<span>null</span>)).equals(snapshot(second)),<span>"子查询第二页"</span>);
System.out.println(<span>"SUBQUERY both pages match; page1 queries=1"</span>);
require(m.count(<span>null</span>)==<span>25</span> && m.joinCount()==<span>40</span>,<span>"计数口径"</span>);
System.out.println(<span>"COUNT orders=25 leftJoinRows=40"</span>);
<span>var</span> <span>filtered</span> <span>=</span> twoQueries(m,<span>0</span>,<span>"wanted"</span>);
require(ids(filtered).equals(List.of(<span>1</span>,<span>7</span>)) && filtered.get(<span>0</span>).lines.size()==<span>3</span>
&& filtered.get(<span>1</span>).lines.size()==<span>4</span> && m.count(<span>"wanted"</span>)==<span>2</span>,<span>"筛订单保留全部明细"</span>);
require(snapshot(filtered).equals(snapshot(m.subquery(<span>0</span>,<span>20</span>,<span>"wanted"</span>))),<span>"筛选子查询"</span>);
System.out.println(<span>"FILTER ids=[1, 7] lines=[3, 4] count=2; both methods match"</span>);
System.out.println(<span>"PASS: 10 assertions"</span>);
}
}
}
}
你们页面筛“包含某类商品的订单”时，会保留这个订单的其他明细吗？可以说说页面约定，这会直接决定筛选条件该放在哪一层。
用可复现实验揭示一对多分页的常见陷阱，给出两种 SQL 改法和断言验证，适合后端开发与 MyBatis 实践者参考。