MySQL 多重左连接限制

Question

如何对具有多个相互依赖的左联接的查询施加左联接限制？请参阅下面关于 LIMIT 的评论:

SELECT 
    a.*,GROUP_CONCAT(c.body SEPARATOR ' ') AS bodies
FROM a   
    LEFT JOIN b ON b.id_a=a.id    
    LEFT JOIN c ON c.id=b.id_c LIMIT 5 # LIMIT 5 Here Does Not Work 
WHERE ...

样本数据集...

DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (  
  `id` int(11) NOT NULL
);

-- Contains relationship between a and c
DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `id_a` int(11) NOT NULL,
  `id_c` int(11) NOT NULL
);

-- Contains body contents
DROP TABLE IF EXISTS `c`;
CREATE TABLE `c` (
  `id` int(11) NOT NULL,
  `body` varchar(2000) NOT NULL
);


-- ----------------------------
-- Sample Records
-- ----------------------------
INSERT INTO `a` VALUES ('1');

INSERT INTO `b` VALUES 
('1','1'), ('1','2'), ('1','3'),
('1','4'), ('1','5'), ('1','6'),
('1','7'), ('1','8'), ('1','9'), 
('1','10');

INSERT INTO `c` VALUES
('1','aa'),('2','bb'), ('3','cc'),
('4','dd'), ('5','ee'), ('6','ff'),
('7','gg'), ('8','hh'), ('9','ii'),
('10','jj');

... 和 SQLFiddle 在 http://sqlfiddle.com/#!9/c1822/12

这里还有一个子查询重写，我试过但没有用，因为无法从嵌套子查询访问外部表，并且失败并显示“where 子句中的未知列 a.id”:http://sqlfiddle.com/#!9/c1822/3

这里还有一个子查询find_in_set rewrite http://sqlfiddle.com/#!9/2d43bb/1它可以工作，但对于大型数据集来说太慢了。

Answer 1

您可以使用变量为子查询中的每一行编号。然后您可以根据该数字过滤掉行。此示例将 left join 限制为最多 3 个具有相同 t1_id 的结果:

select  *
from    table1 t1
left join
        (
        select  @rn := case when t1_id = @prev_id then @rn + 1 else 1 end rn
        ,       @prev_id := t1_id
        ,       t2.*
        from    table2 t2
        order by
                t1_id
        ) t2rn
on      t2rn.t1_id = t1.id
        and t2rn.rn < 4  -- At most 3 rows

Example at SQL Fiddle.