sql - 如何优化具有多个子查询的 SELECT 语句

Question

我正在尝试优化一个查询，但没有取得太大的成功。有两个表，一个包含主要数据，另一个包含特定事件发生时间的时间戳。这些表是使用 adID 公共(public)键的关系表。我正在尝试执行一个查询，从主表中提取所有时间戳和其他数据。我让它工作，但我正在尝试优化以使其运行得更快。

SELECT a.ID,a.repID,a.artistID,
(
    SELECT TOP 1 c.timestamp
    FROM Tracking AS c
    WHERE statusID = 4
    AND c.ID = a.ID
    ORDER BY c.timestamp ASC

) 
AS created,
(
    SELECT TOP 1 d.timestamp
    FROM Tracking AS d
    WHERE statusID = 5
    AND d.ID = a.ID
    ORDER BY d.timestamp ASC
)
AS claimed,
(
    SELECT TOP 1 p.timestamp
    FROM Tracking AS p
    WHERE statusID = 6
    AND p.ID = a.ID
    ORDER BY p.timestamp ASC
)
AS proof,
(
    SELECT TOP 1 v.timestamp
    FROM Tracking AS v
    WHERE statusID = 8
    AND v.ID = a.ID
    ORDER BY v.timestamp ASC
)
AS approved,
(
    SELECT count(ID)
    FROM Tracking AS t
    WHERE statusID = 6
    AND t.ID = a.ID
)
AS proofcount
FROM Advertising AS a
WHERE a.statusID = 8

在此方面的任何帮助表示赞赏。我对 SQL Server 不太熟悉，所以我不太精通优化此类查询。

Answer 1

您应该能够使用以下内容:

SELECT a.ID,
  a.repID,
  a.artistID,
  min(case when t.statusID = 4 then t.timestamp end) created,
  min(case when t.statusID = 5 then t.timestamp end) claimed,
  min(case when t.statusID = 6 then t.timestamp end) proof,
  min(case when t.statusID = 8 then t.timestamp end) approved,
  count(case when t.statusID = 6 then id end) proofcount
FROM Advertising AS a
LEFT JOIN Tracking t
  on a.id = t.id
WHERE a.statusID = 8
GROUP BY a.ID, a.repID, a.artistID;