我设计了一个数据库,其中存储了执行流程所需的所有信息。我所说的“过程”是指一系列的多个步骤,或者换句话说,一个配方。每个配方(在我的例子中是任务)包含几个步骤。给定的步骤可以出现在不同的配方中。
这是我拥有的 3 个表的示例(带有随机数据):
工艺表
+----+------+------+--------+-----------+
| ID | Task | Step | NextID | StartHere |
+----+------+------+--------+-----------+
| 1 | 2 | 1 | 4 | TRUE |
| 2 | 2 | 5 | 8 | FALSE |
| 3 | 4 | 5 | 9 | FALSE |
| 4 | 2 | 2 | 2 | FALSE |
| 5 | 4 | 2 | 6 | FALSE |
| 6 | 4 | 4 | 3 | FALSE |
| 7 | 4 | 1 | 5 | TRUE |
| 8 | 2 | 15 | 0 | FALSE |
| 9 | 4 | 15 | 0 | FALSE |
+----+------+------+--------+-----------+
任务表
+--------+--------------+
| TaskID | TaskName |
+--------+--------------+
| 1 | Buy Disk |
| 2 | Play Disk |
| 3 | Buy Digital |
| 4 | Play Digital |
+--------+--------------+
台阶稳定
+--------+-----------------+
| StepID | StepName |
+--------+-----------------+
| 1 | Turn Console On |
| 2 | Log In |
| 4 | Insert Disk |
| 5 | Open Game |
| 15 | Enjoy |
+--------+-----------------+
这是上面的流程表,但包含步骤和任务的实际名称。用这张表可能更容易理解:
+----+--------------+-----------------+--------+-----------+
| ID | Task | Step | NextID | StartHere |
+----+--------------+-----------------+--------+-----------+
| 1 | Play Disk | Turn Console On | 4 | TRUE |
| 2 | Play Disk | Open Game | 8 | FALSE |
| 3 | Play Digital | Open Game | 9 | FALSE |
| 4 | Play Disk | Log In | 2 | FALSE |
| 5 | Play Digital | Log In | 6 | FALSE |
| 6 | Play Digital | Insert Disk | 3 | FALSE |
| 7 | Play Digital | Turn Console On | 5 | TRUE |
| 8 | Play Disk | Enjoy | 0 | FALSE |
| 9 | Play Digital | Enjoy | 0 | FALSE |
+----+--------------+-----------------+--------+-----------+
如图所示,在Process表中,每条记录(行)代表任务和步骤的组合。NextID
遵循配方时,该列包含下一个任务-步骤组合的 ID。一NextID
的0
标志任务的结束。的StartHere
列是一个布尔指定根据任务要执行的第一个步骤。
按照这个逻辑,为了“玩数字游戏”,你需要先打开控制台,然后登录,然后打开游戏,最后享受你的游戏会话。
我的问题是:我想找到一种方法来查询完成给定任务所需的所有步骤,并按照适当的顺序(根据Process表执行它们的顺序)对它们进行排序。有人知道如何做到这一点(如果可能,在一个查询中)?
基本上,查询应先拿到StepName
其中行的StartHere
是TRUE
,再拿到StepName
有一个行的ID
等于NextID
前一行等。
我想出了以下查询,但当然这并没有给我正确的顺序:
SELECT Step.StepName
FROM Step
INNER JOIN Process ON Step.StepID = Process.Step
INNER JOIN Task ON Process.Task = Task.TaskID
WHERE Task.TaskID = 4
ORDER BY Process.NextID
欢迎任何提示的帮助!
谢谢
编辑:根据评论中的要求,如果为“播放数字”任务完成了此类查询的预期结果:
+-----------------+
| StepNane |
+-----------------+
| Turn Console On |
| Log In |
| Open Game |
| Enjoy |
+-----------------+
使用CTE,以Next id的形式获取子节点原理...
declare @mytable table
(id int,
task varchar(50),
step varchar(50),
nextid int
)
insert into @mytable
values
( 1 ,'Play Disk ','Turn Console On ', 4 ),
( 2 ,'Play Disk ','Open Game ', 8 ),
( 3 ,'Play Digital ','Open Game ', 9 ),
( 4 ,'Play Disk ','Log In ', 2 ),
( 5 ,'Play Digital ','Log In ', 6 ),
( 6 ,'Play Digital ','Insert Disk ', 3 ),
( 7 ,'Play Digital ','Turn Console On ', 5 ),
( 8 ,'Play Disk ','Enjoy ', 0 ),
( 9 ,'Play Digital ','Enjoy ', 0 )
;with mycte
as
(
select * from @mytable where id = 1 -- to start from this ID, if you want this your START here column with true then eventually you will have multiple outputs
union all
select t.* from @mytable t
inner join mycte c on c.nextid = t.id
)
select * from mycte;
结果,从 1 开始
id task step nextid
1 Play Disk Turn Console On 4
4 Play Disk Log In 2
2 Play Disk Open Game 8
8 Play Disk Enjoy 0
结果,从7开始
id task step nextid
7 Play Digital Turn Console On 5
5 Play Digital Log In 6
6 Play Digital Insert Disk 3
3 Play Digital Open Game 9
9 Play Digital Enjoy 0
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句