SQL 将一对多查询子查询到视图中的各个数据列的最佳方法

Question

>我有这样的数据：

WorkTable
---------
WorkId  
1  
2  
ProcessTable
WorkId    Process    ProcessDate  
1         Receive    1/20/2017  
1         Triage     1/21/2017  
1         Triage     1/22/2017  
2         Receive    1/20/2017  

我想创建一个如下所示的视图：

WorkId    Received    Triaged  
1         1/20/2017   1/22/2017  
2         1/20/2017   NULL  

我只需要最新的流程执行日期，因此使用 Id 1 时，我选择 1/22 进行分类，因为它是最新的

我当前的查询看起来像这样，但效率非常低

Select w.WorkId,    
(SELECT     TOP (1) ProcessDate
     FROM          ProcessTable
     WHERE      (w.WorkId = WorkId) AND (Process = 'Receive')
     ORDER BY ProcessDate DESC) AS Received,
(SELECT     TOP (1) ProcessDate
     FROM          ProcessTable
     WHERE      (w.WorkId = WorkId) AND (Process = 'Triage')
     ORDER BY ProcessDate DESC) AS Triaged
From WorkTable w

我能做些什么来加快速度？

Answer 1

可以使用联接和条件聚合。 但是，这很棘手，因为排序是按CreatedDate但您想要的字段是ProcessDate。

我建议您保持查询不变并创建最佳索引索引：ProcessDate(WorkId, Process, CreatedDate, ProcessDate) 。

Answer 2

更新：

使用外部apply()：

select 
    w.WorkId
  , r.Received
  , r.ReceivedBy
  , t.Triaged
  , t.TriagedBy
from WorkTable
  outer apply (
    select top 1 
        Received = i.ProcessDate 
      , ReceivedBy = i.UserName
    from ProcessTable i
    where i.WorkId = w.WorkId
      and i.Process = 'Receive'
    order by i.ProcessDate desc
    ) r
  outer apply (
    select top 1 
        Triaged = i.ProcessDate 
      , TriagedBy = i.UserName
    from ProcessTable i
    where i.WorkId = w.WorkId
      and i.Process = 'Triage'
    order by i.ProcessDate desc
    ) t

---

使用条件聚合：

select 
    WorkId 
  , Received = max(case when Process='Receive' then ProcessDate end)
  , Triaged  = max(case when Process='Triage'  then ProcessDate end)
from ProcessTable w
group by WorkId

Rextester 演示：http://rextester.com/HNB72821

返回(RexTester 重新格式化日期时间值，这被格式化回来(：

+--------+------------+------------+
| WorkId |  Received  |  Triaged   |
+--------+------------+------------+
|      1 | 2017-01-20 | 2017-01-22 |
|      2 | 2017-01-20 | NULL       |
+--------+------------+------------+

---

或者像这样pivot()：

select 
    WorkId 
  , Receive
  , Triage 
from ProcessTable w
pivot (max(ProcessDate) for Process in ([Receive],[Triage]))p

如果ProcessTable中还有其他未使用的列，则可能需要仅选择pivot()所需的列，如下所示：

select 
    WorkId 
  , Receive
  , Triage 
from (select WorkId, Process, ProcessDate from ProcessTable) w
pivot (max(ProcessDate) for Process in ([Receive],[Triage]))p