子查询中存在多部分标识符问题的 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 ...

如果您对此有任何帮助或暗示，我将不胜感激，

聚苯乙烯

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 没有意义。