如何在不使用 HAVING 子句的情况下仅选择不相关的行

Question

我的“序列号”表的数据如下：

序列号	日期	状态
A00010	03.03.2022	BAD
A00010	04.03.2022	GOOD
A00011	05.03.2022	BAD
A00012	06.03.2022	BAD

我只想选择那些实际状态不佳的序列号。 因此，我期望的结果只有 A00011 和 A00012。这是因为 A00010 最新（查看日期）状态为“良好”。 查询应该只返回不同的值（这里我使用按序列分组）。

我发现的唯一解决方案是使用 HAVING 子句：

SELECT [Serial], MAX([Date]),  MAX([Status])
FROM [Serials]
GROUP BY [Serial]
HAVING MAX([Status]) != 'GOOD'

我宁愿避免使用它，因为它使用更多资源（特别是在扫描更大的表时）。此外，表中的大多数序列无论如何都会将最新记录设置为“GOOD”，因此当前查询将拉取几乎整个表，然后 HAVING 将仅裁剪其中的大部分。对于大表来说，这会造成很大的资源浪费。 因此，如果有人可以帮助我找到执行相同操作但仅使用 WHERE 子句的查询。 谢谢。

Answer 1

一种选择是使用 ROW_NUMBER() 查找每个序列的最后一行，然后过滤掉状态良好的那些行。

WITH
  sorted AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY Serial ORDER BY [date] DESC)   AS serial_row   
  FROM
    serials
)
SELECT
  *
FROM
  sorted
WHERE
      serial_row  = 1
  AND status     != 'GOOD'

Answer 2

试试这个：

Select Serial
From Serials s
Where s.Date = 
   (Select Max(Date) From Serials
        Where Serial = s.Serial) 
   And Status != 'Good'

如果表中可能存在多个具有相同序列号和日期的记录，则添加“不同”一词。

Select Distinct Serial
From Serials s
Where s.Date = 
    (Select Max(Date) From Serials
        Where Serial = s.Serial) 
   And Status != 'Good'

要在第三条注释中也解决您的请求（仅选择以前良好的序列号），只需将其添加为Where 子句的附加谓词：

Select Distinct Serial
From Serials s
Where s.Date = 
    (Select Max(Date) From Serials
        Where Serial = s.Serial) 
   And Status != 'Good'
   And exists                  -- This predicate ensures that only 
      (Select * from Serial x   -- previously 'GOOD' serials 
       Where serial = s.Serial  -- will appear
          and Date < (Select Max(Date) From Serials
                      Where Serial = s.Serial) 
          and status = 'GOOD')                        

基本上，您想要做的事情可以直接转换为 SQL：

您需要这些序列的 [distinct] 列表，这些序列是每个特定序列的最后时间记录的集合，经过过滤以仅包含不是好人。
因此，您创建一个子表，其中仅包含日期是该序列的最新日期的记录，然后按状态对其进行过滤以仅包含不好的记录，然后从该表中提取序列值。

根据表的大小，如果您在日期属性上有索引，这将非常有效（或更好）。