限制多行的左連接

由於 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'

如果我們嘗試使用下面的查詢來達到預期的結果，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 個（或更多）限制行來獲得上述結果的替代方法是什麼？

您使用的是什麼版本的 MySQL？MySQL 8 支持 LATERAL，您需要能夠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;

Concider 將 ORDER BY 添加到您的子選擇中。

做類似事情的其他方法是使用視窗函式，例如：

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 對子查詢進行排序，每行將 row_num 增加 1，並且一旦 parent_id 更改，將 row_num 重置為 1。

你可以閱讀更多關於這個想法的資訊，例如：mysql-row_number

引用自：https://dba.stackexchange.com/questions/239950