根据状态排除行

SELECT
  Name,
  Number,
  Revision,
  Status  
FROM
  Document
WHERE
  (Status = 'Pending Effective') OR
  (
    (Status = 'Effective') AND
    NOT EXISTS (
      SELECT * FROM Document AS d WHERE (Document.Number = d.Number) AND
      (d.Status = 'Pending Effective')
    )  
  )

SQL查询选择“待定生效”或“生效”的文档(前提是没有相同编号的待定文档)。

此示例使用子查询。在子查询中,我们查找具有相同编号但状态为“待定生效”的文档。如果找到此类文档,我们不会将状态为“有效”的文档包含在结果集中。

您可以按状态对每个文档编号的行进行排序,然后仅返回适用的行:

SELECT name, number, revision, status
FROM   (SELECT name, number, revision, status,
               ROW_NUMBER() OVER (PARTITION BY number ORDER BY status DESC) AS rn
        FROM   document
        WHERE  status IN ('Pending Effective', 'Effective)) t
WHERE  rn = 1

注:
排序适用于这两种状态,因为“Pending Effective”在字典上位于“Effective”之后。如果以后想向查询中添加其他状态,可以按 case 表达。

WITH filtered_docs AS (
     SELECT Name, Number, Revision, Status 
     FROM DOCUMENT
     WHERE Status IN (‘Pending Effective’, ‘Effective’)
),
ranked_docs AS (
           SELECT *, ROW_NUMBER() OVER ( 
                         PARTITION BY Number 
                         ORDER BY
                         CASE 
                             WHEN Status = ‘Pending Effective’ THEN 1
                             ELSE 2
                         END
                    ) AS rank
           FROM filtered_docs
 )
 SELECT * 
 FROM ranked_docs
 WHERE rank = 1;

在上述查询中,首先根据状态过滤记录。之后,ROW_NUMBER()根据排序顺序将秩为1分配给每个数字的第一行,最后我们选择秩为1的行。