MySQL实现先JOIN再WHERE后按Ticket对象分页（LIMIT OFFSET）

表结构与数据 #

my_tickets表 #

+-----------+---------+
| ticket_id | user_id |
+-----------+---------+
| 186       | 2       |
| 187       | 2       |
| 188       | 2       |
| 253       | 33      |
| 254       | 33      |
| 256       | 33      |
| 261       | 33      |
| 262       | 33      |
| 263       | 33      |
| 1573      | 7       |
| 1597      | 7       |
| 1748      | 7       |
+-----------+---------+

my_users表 #

+----+---------+
| id | name    |
+----+---------+
| 2  | user_2  |
| 7  | user_7  |
| 33 | user_33 |
+----+---------+

my_data表 #

+----+-----------+------------+
| id | ticket_id | data       |
+----+-----------+------------+
| 1  | 186       | data_186_1 |
| 2  | 186       | data_186_2 |
| 3  | 187       | data_187_1 |
| 4  | 253       | data_253_1 |
| 5  | 253       | data_253_2 |
| 6  | 253       | data_253_3 |
| 7  | 254       | data_254_1 |
+----+-----------+------------+

需求说明 #

关联三张表（关联条件：my_tickets.user_id <=> my_users.id，my_tickets.ticket_id <=> my_data.ticket_id），筛选出my_tickets.user_id=33的记录，再按Ticket对象（每个ticket_id为一个独立对象，而非单条数据行）取前3个Ticket的关联结果实现分页，预期输出如下：

+-----------------------+---------------------+----------------+---------------+
| my_tickets__ticket_id | my_tickets__user_id | my_users__name | my_data__data |
+-----------------------+---------------------+----------------+---------------+
| 253                   | 33                  | user_33        | data_253_1    |
| 253                   | 33                  | user_33        | data_253_2    |
| 253                   | 33                  | user_33        | data_253_3    |
| 254                   | 33                  | user_33        | data_254_1    |
| 256                   | 33                  | user_33        | NULL          |
+-----------------------+---------------------+----------------+---------------+

原SQL问题分析 #

原查询返回空表的核心原因：子查询SELECT ticket_id FROM my_tickets LIMIT 3 OFFSET 0直接从全表取前3个ticket_id（186、187、188），这些Ticket属于user_id=2，和外层筛选的user_id=33无交集，因此没有匹配结果。

正确SQL实现 #

SELECT 
    t.ticket_id AS my_tickets__ticket_id,
    t.user_id AS my_tickets__user_id,
    u.name AS my_users__name,
    d.data AS my_data__data
FROM (
    -- 先筛选user_id=33的Ticket，再按Ticket对象分页取前3个
    SELECT ticket_id, user_id 
    FROM my_tickets 
    WHERE user_id = 33 
    LIMIT 3 OFFSET 0
) AS t
LEFT JOIN my_users u ON t.user_id = u.id
LEFT JOIN my_data d ON t.ticket_id = d.ticket_id

逻辑说明 #

1. 内层子查询先从my_tickets中筛选出user_id=33的记录，用LIMIT 3 OFFSET 0获取前3个Ticket对象（对应ticket_id为253、254、256）；
2. 将这3个Ticket作为基础表，分别与my_users、my_data做左关联，确保即使Ticket没有对应my_data记录（如256）也能返回NULL值；
3. 最终得到每个Ticket对应的所有关联数据行，完全符合预期分页需求。

内容的提问来源于stack exchange，提问作者ihtus