限制多行的左联接

Question

由于MySql不支持子查询中的主别名引用，因此创建多行查询有点困难，因此必须限制左联接表的行数。

让我们来概述一下简单的例子。

假设有两个表：

CREATE TABLE `items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `parent` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `sub_items` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `child` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `parent_id` INT NOT NULL,
  PRIMARY KEY (`id`));

有以下内容：

INSERT INTO `items` (`parent`) VALUES ('Main item1');
INSERT INTO `items` (`parent`) VALUES ('Main item2');
INSERT INTO `items` (`parent`) VALUES ('Main item3');

INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item11', '1');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item12', '1');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item13', '1');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item21', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item22', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item23', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item24', '2');
INSERT INTO `sub_items` (`child`, `parent_id`) VALUES ('Child Item31', '3');

现在，让我们创建如下所示的查询：

SELECT i.*, si.child, si.parent_id FROM items as i 
LEFT JOIN sub_items as si
ON i.id = si.parent_id;

其结果将是：

'1', 'Main item1', 'Child Item11', '1'
'1', 'Main item1', 'Child Item12', '1'
'1', 'Main item1', 'Child Item13', '1'
'2', 'Main item2', 'Child Item21', '2'
'2', 'Main item2', 'Child Item22', '2'
'2', 'Main item2', 'Child Item23', '2'
'2', 'Main item2', 'Child Item24', '2'
'3', 'Main item3', 'Child Item31', '3'

现在的挑战是限制联接表行(在本例中限制为2行)：

'1', 'Main item1', 'Child Item11', '1'
'1', 'Main item1', 'Child Item12', '1'
'2', 'Main item2', 'Child Item21', '2'
'2', 'Main item2', 'Child Item22', '2'
'3', 'Main item3', 'Child Item31', '3'

如果我们尝试使用query来获得预期的结果，MySQL会抛出错误：

SELECT i.*, si.child, si.parent_id FROM items as i 
LEFT JOIN (SELECT * FROM sub_items WHERE parent_id = i.id LIMIT 2) as si
ON i.id = si.parent_id;

那么，对于每个左联接子查询，有2行(或更多)限制行才能获得上述结果的替代方法是什么？

Answer 1

您使用的是什么版本的MySQL？MySQL 8支持横向支持，您需要能够引用派生表中的i：

SELECT i.*, si.child, si.parent_id 
FROM items as i 
LEFT JOIN LATERAL (SELECT *
                   FROM sub_items 
                   WHERE parent_id = i.id 
                   LIMIT 2) as si
    ON i.id = si.parent_id;

在您的子选择中添加订单。

其他类似的方法是使用窗口函数，例如：

row_number() over (partition by ... order by ...) as rn

在外层过滤rn。

然而，MySQL 8只支持窗口函数，但您可以使用变量来模拟它们：

SELECT i.*, si.child, si.parent_id, si.rn 
FROM items as i 
LEFT JOIN (
    SELECT c.*, 
        @row_number:=CASE WHEN @parent_id = parent_id
                          THEN @row_number + 1
                          ELSE 1
                     END AS rn,      
        @parent_id := parent_id
    FROM sub_items as c
    CROSS JOIN (select @row_number := 1) as x
    CROSS JOIN (select @parent_id := -1) as y
    ORDER BY parent_id
) as si
    ON i.id = si.parent_id
    AND si.rn <= 2
;

这样做的目的是按parent_id排序子查询，为每行增加1次row_num，一旦parent_id更改，将row_num重置为1。

你可以在例如：mysql行_数上读到更多关于这个想法的文章。

Answer 2

另一种甚至在古代版本(如5.1)中也有效的方法是模仿LATERAL连接，我称之为“穷人的”横向连接/外部应用。

这是我们想模仿的横向连接：

SELECT i.*, si.child, si.parent_id 
FROM items AS i 
    LEFT JOIN LATERAL
      ( SELECT s.*
        FROM sub_items AS s
        WHERE s.parent_id = i.id 
        ORDER BY s.id             -- assuming we don't want arbitrary results
        LIMIT 2
      ) AS si
    ON TRUE ;     -- this is redundant: ON i.id = si.parent_id ;

可怜的男人的版本：

SELECT i.*, si.child, si.parent_id 
FROM items AS i 
    LEFT JOIN sub_items AS si
    ON  si.parent_id = i.id
    AND si.id <=                     -- 1. three places where 
      COALESCE(
      ( SELECT s.id                  -- 2. same column is used 
        FROM sub_items AS s
        WHERE s.parent_id = i.id 
        ORDER BY s.id                -- 3. as this ORDER BY 
        
        LIMIT 1 OFFSET 1             -- pick the 2nd.
                                     -- if you want LIMIT 5, 
                                     -- you need LIMIT 1 OFFSET 4 here
      ), 
      4294967295)                    -- this would depend on the type
                                     -- of the column used in ORDER BY
                                     -- and on ASC/DESC
 ;

dbfiddle.uk测试

缺点：

• 写(读/理解)很复杂，特别是当要求的限制是2或更大的时候。

优势：

• 在任何地方都有效--甚至在古代版本中。
• 当不同的items (父表)的数量从小到中等时，性能就足够了。