我想按其分支数据的顺序获取数据(如果存在)

Question

嗨，我在一个表中有数据作为问题表

QuestionID  QuestionDescription
2           This is test
3           test is tst
4           3
6           5
17          6
18          7
19          8
20          9
5           4

并在一个表中问题分支表作为

QuestionBranchingID QuestionID  Response    NextQuestionID  ParentQuestionID
1                      3        True        5               3
2                      3        False       6               3
7                      5        True        19              3
8                      5        False       20              3
9                      18       True        17              18
10                     18       False       4               18

因此，如果 QuestionBranching 表中存在任何 QuestionID，则"选择联接"查询应按该顺序提取数据。 例如： 如果 QuestionBranch 表中存在 QuestionID，则 NextQuestionID 将是序列中的下一个。 如果不是，则正常流动。

所以我正在寻找的预期结果是：

QuestionID
2
3(if it exists in QuestionBranching then NextQuestionID will be next i.e. '5')
5
6
19
20
18
17
4

Answer 1

试试这个：

select isnull(b.NextQuestionID,q.QuestionID) as QuestionID
from Question q
left join QuestionBranching b on q.QuestionID=b.QuestionID

Answer 2

也许这可以帮助你

declare @question table (QuestionID int, QuestionDescription varchar(100))
declare @branching table (BranchingID int, QuestionID int, Response bit, NextQuestionID int)
insert into @question values (2, 'this is a test'), (3, 'test is tst'), (4, '3'), (6, '5'), (17, '6'), (18, '7'), (19, '8'), (20, '9'), (5, '4')
insert into @branching values (1, 3, 1, 5), (2, 3, 0, 6), (7, 5, 1, 19), (8, 5, 0, 20)
select t.nq
from   ( select q.QuestionID, q.QuestionID as nq 
from   @question q
union all
select isnull((select b2.QuestionID from @branching b2 where b2.NextQuestionID = b.QuestionID), b.QuestionID) as QuestionID, b.NextQuestionID as nq
from   @branching b
) t
order by t.QuestionID, t.nq