循环结果的 SQL 查询

Question

我知道这是可以做到的，不应该太难，但我的大脑现在什么也没处理。

我有两个表，它们共同用于操作和响应。响应记录本身就是一个操作，所以我有以下内容：

select a.[Action], b.Response, c.Action, d.Response, e.Action,
       f.Response, g.Action, h.Response  
from ValidRecruitmentAction a 
  LEFT OUTER JOIN ValidRecruitmentAction_Response b on a.[key] = b.[actionkey] 

我正在尝试输出所有链接。因此，在第一个操作之后，我得到了它的响应，但是该操作的响应呢？

我最终这样做：

select a.[Action], b.Response, c.Action, d.Response, e.Action,
       f.Response, g.Action, h.Response 
from ValidRecruitmentAction a
  LEFT OUTER JOIN ValidRecruitmentAction_Response b on a.[key] = b.[actionkey]
  LEFT OUTER JOIN ValidRecruitmentAction c on c.[Key] = b.FollowOnActionKey
  LEFT OUTER JOIN ValidRecruitmentAction_Response d on c.[key] = d.[actionkey]
  LEFT OUTER JOIN ValidRecruitmentAction e on e.[Key] = d.FollowOnActionKey
  LEFT OUTER JOIN ValidRecruitmentAction_Response f on e.[key] = f.[actionkey]
  LEFT OUTER JOIN ValidRecruitmentAction g on g.[Key] = f.FollowOnActionKey
  LEFT OUTER JOIN ValidRecruitmentAction_Response h on g.[key] = h.[actionkey]

我知道有一种更简单的方法可以做到这一点。谁能提醒我该怎么做，可能需要 CTE。我希望在一个查询中执行此操作，而不是作为过程的一部分。

Answer 1

我的大脑今天也不是很强壮，但这是我设法从中得到的。

With CTE as
(
Select a.[key] as Action_key
from ValidRecruitmentAction a
where a.[action = 'My first action'
UNION ALL
Select b.FollowOnActionKey
from ValidRecruitmentAction_Response b
    INNER JOIN CTE ON CTE.Action_key = b.[actionkey]
)
Select  a.[Action], b.Response
from ValidRecruitmentAction a
    INNER JOIN CTE ON CTE.Action_key = a.[key]
    LEFT OUTER JOIN ValidRecruitmentAction_Response b on a.[key] = b.[actionkey]

Answer 2

它不会表现得很出色，但您可以加入多个 clasu，因此您的 ON 子句可能会a.[key] = b.[actionkey] OR c.[Key] = b.[actionkey] OR e.[Key] ...等等。

但是，在您的示例/情况下，我宁愿建议您走UNION路线。
所以像这样：

   SELECT  a.[Action] ,
            b.Response                
    FROM    ValidRecruitmentAction a
            INNER JOIN ValidRecruitmentAction_Response b ON a.[key] = b.[actionkey]
    UNION
    SELECT  a.[Action] ,               
            c.Response
    FROM    ValidRecruitmentAction a
            INNER JOIN ValidRecruitmentAction b ON a.[Key] = b.FollowOnActionKey
......insert all your remaining cases as unions

然后，您可以为每个案例的每个 UNIONS 添加 WHERE 子句;
而且很可能使它们的性能比您最终可能尝试在一个连接中完成所有操作的性能要好得多。