时间戳

首页 > 解决方案 > 顺序自连接 SQL

mysql - 顺序自连接 SQL

问题描述

在此处输入图像描述

我有一张如上所述的桌子。如何使用自联接按顺序获取输出，如下所示？

  1. 将烤箱预热到220摄氏度。
  2. 四个土豆去皮。
  3. 将土豆切成片。
  4. 将切好的土豆抹上油。
  5. 在预热好的烤箱里烤20分钟。
  6. 用盐和胡椒调味热切片。
select A.content
from recipe A
left join 
(select 
CASE WHEN previous_id IS NULL THEN 0 ELSE previous_id END previous_id, 
CASE WHEN next_id IS NULL THEN 0 ELSE next_id END next_id 
from recipe order by previous_id) B
on B.next_id = A.id;

我如何在这里使用previous_id？

用于重新创建表的 DDL 语句

create table recipes (id int, content varchar(50) , previous_id int, next_id int);
insert into recipes (id, content, previous_id, next_id) values (1, 'Preheat an oven to 220 degrees C.', NULL, 2);
insert into recipes (id, content, previous_id, next_id) values (2, 'Peel four potatoes.', 1, 4);
insert into recipes (id, content, previous_id, next_id) values (3, 'Toss sliced potatoes with oil.', 4, 6);
insert into recipes (id, content, previous_id, next_id) values (4, 'Cut potatoes into slices.', 2, 3);
insert into recipes (id, content, previous_id, next_id) values (5, 'Season the hot slices with salt and pepper.', 6, NULL);
insert into recipes (id, content, previous_id, next_id) values (6, 'Bake in the preheated oven for 20 minutes.', 3, 5);

标签： mysqlsql

解决方案

您不需要使用 JOIN。您只需要从表中选择所有数据（如果这只是表中的数据）和 ORDER BY previous_id。ORDER BY 总是将 NULL 值放在最前面（如果您不按 DESC 排序），然后您将按 previous_id 升序获得步骤。

SELECT * FROM recipes ORDER BY previous_id

看看这个演示。

推荐阅读