MySQL5.6如何按test_composite表字段限制关联查询questions的条数

开发者阿泽

2026-10-20

你写的SQL报错是因为MySQL的LIMIT子句仅支持常量参数，不允许引用外层查询的字段值，且5.6版本无LATERAL JOIN特性，可通过关联子查询计数法实现需求，具体实现如下：

SELECT 
    q.*
FROM 
    test_composite tc
INNER JOIN tests t ON tc.id_include_test = t.id
INNER JOIN questions q ON q.test_id = t.id
WHERE (
    -- 子查询统计同test下比当前question id更小的数量，等价于行号
    SELECT COUNT(*) 
    FROM questions q2 
    WHERE q2.test_id = q.test_id 
      AND q2.id <= q.id
) <= tc.questions_quantity
ORDER BY t.id, q.id;

逻辑说明

子查询会对每个test下的question按id升序生成隐式行号，编号从1开始递增
外层查询直接筛选行号小于等于test_composite表中questions_quantity限制值的记录，就能实现每个test返回指定条数question的需求

自定义调整规则

如果需要调整每个test下question的选取规则，只需要修改子查询的比较逻辑即可：

按question id倒序取最新N条：把q2.id <= q.id改为q2.id >= q.id
随机取N条：先对questions表做随机排序再关联，写法如下：

SELECT 
    q.*
FROM 
    test_composite tc
INNER JOIN tests t ON tc.id_include_test = t.id
INNER JOIN (SELECT * FROM questions ORDER BY RAND()) q ON q.test_id = t.id
WHERE (
    SELECT COUNT(*) 
    FROM questions q2 
    WHERE q2.test_id = q.test_id 
      AND q2.id <= q.id
) <= tc.questions_quantity
ORDER BY t.id;