子查询中具有多部分标识符问题的 SQL 查询

Question

我有一个查询应该返回“状态”持续时间条目的总和。持续时间是通过使用 datediff(n, datestamp, （返回结束当前状态的日期戳的子查询，即在锁定的条目之后找到下一个合适的“状态更改”条目）来计算的。

我的问题是以下查询返回一个多-部分标识符错误

• INC 表给了我 我正在寻找与以下内容相关的“INCIDENT_NUMBER” 其他表中的“NUMBER”
• ACTM1 保存所有 DATESTAMP 条目
• ACTA1 通过“THENUMBER”与 ACTM1 相关，并且它保存有关条目是否适合状态更改的所有信息

代码：

SELECT SUM(DATEDIFF(n, ACTM1.DATESTAMP, END_DATESTAMP_TABLE.END_DATESTAMP))
FROM INC                LEFT OUTER JOIN
  ACTM1   ON INC.INCIDENT_NUMBER = ACTM1.NUMBER  LEFT OUTER JOIN
  ACTA1   ON ACTM1.THENUMBER  = ACTA1.THENUMBER  LEFT OUTER JOIN
/**/
    (SELECT  ACTM1_1.NUMBER, ACTM1_1.DATESTAMP AS END_DATESTAMP
  FROM ACTM1 AS ACTM1_1               LEFT OUTER JOIN
/**/
      (SELECT ACTM1_1_1.NUMBER, MIN(ACTM1_1_1.THENUMBER) AS FOLLOWUP_THENUMBER
    FROM ACTM1 AS ACTM1_1_1
    WHERE  (ACTM1_1_1.THENUMBER > /**/ ACTM1_1.THENUMBER)/*I think here lies the problem*/
      AND (ACTM1_1_1.[TYPE]  IN ('Open', 'Status Change', 'Resolved', 'Closed')))
    AS FOLLOWUP_THENUMBER_TABLE
/**/
            ON ACTM1_1.NUMBER = FOLLOWUP_THENUMBER_TABLE.NUMBER)
  AS END_DATESTAMP_TABLE
/**/
            ON ACTM1.NUMBER = END_DATESTAMP_TABLE.NUMBER
WHERE ...

我将不胜感激任何有用的评论或提示你可以给我这个，

PS

Answer 1

左侧联接关系无法引用右侧，因此这是非法的：

SELECT ...
FROM A
JOIN (SELECT ...FROM ... WHERE ... = A.Field) AS B ON A.ID = B.ID;

请改用 APPLY 运算符：

SELECT ...
FROM A
APPLY (SELECT ...FROM ... WHERE ... = A.Field AND ID = A.ID) AS B;

在您的情况下可能如下所示：

SELECT SUM(DATEDIFF(n, ACTM1.DATESTAMP, END_DATESTAMP_TABLE.END_DATESTAMP))
FROM INC                
LEFT OUTER JOIN ACTM1 ON INC.INCIDENT_NUMBER = ACTM1.NUMBER  
LEFT OUTER JOIN ACTA1 ON ACTM1.THENUMBER  = ACTA1.THENUMBER  
LEFT OUTER JOIN (
   SELECT  ACTM1_1.NUMBER, ACTM1_1.DATESTAMP AS END_DATESTAMP
   FROM ACTM1 AS ACTM1_1
   OUTER APPLY (
      SELECT ACTM1_1_1.NUMBER, /* MIN(ACTM1_1_1.THENUMBER) */ AS FOLLOWUP_THENUMBER
        FROM ACTM1 AS ACTM1_1_1
        WHERE  (ACTM1_1_1.THENUMBER > ACTM1_1.THENUMBER)
        AND (ACTM1_1.NUMBER = FOLLOWUP_THENUMBER_TABLE.NUMBER)
        AND (ACTM1_1_1.[TYPE]  IN ('Open', 'Status Change', 'Resolved', 'Closed'))
    ) AS FOLLOWUP_THENUMBER_TABLE
) AS END_DATESTAMP_TABLE ON ACTM1.NUMBER = END_DATESTAMP_TABLE.NUMBER

显然，内部查询中的 MIN 没有任何意义。

Answer 2

我会重写查询以根本不使用子查询：

SELECT
     SUM(DATEDIFF(n, A1.datestamp, A2.datestamp))

FROM
     INC AS I
INNER JOIN ACTM1 AS A1 ON
     A1.number = INC.incident_number
INNER JOIN ACTM1 AS A2 ON
     A2.number > A1.number AND
     A2.type IN ('Open', 'Status Change', 'Resolved', 'Closed')
LEFT OUTER JOIN ACTM1 AS A3 ON
     A3.number > A1.number AND
     A3.type IN ('Open', 'Status Change', 'Resolved', 'Closed') AND
     A3.number < A2.number
WHERE
     A3.number IS NULL

我无法完全对您的语句进行逆向工程。我不知道你是否需要左连接，而且我没有看到 ACTA1 实际在哪里使用，所以我把它省略了。因此，您可能需要调整上述内容。但总体思路是找到具有更大数字的行，该行具有您需要的类型，但没有其他行 (A3) 具有正确的类型，并且数字位于两个数字之间。