sql-server - 如果组中没有记录比特定数据更新，则分组

Question

我在下面的 sql 查询中遇到问题。

SELECT 
       MAX([DL_Id]) as [DL_Id]
      ,MAX([DL_CreatedBy]) as [DL_CreatedBy]
      ,MAX([DL_Created]) as [DL_Created]
      ,MAX([DL_ModifiedBy]) as [DL_ModifiedBy]
      ,MAX([DL_Modified]) as [DL_Modified]
      ,[DL_StandardLetter]
      ,count(1)
  FROM [DB].[dbo].[DL_StandardLetterStatistics]

  where (DL_Modified < '2018-01-01' or DL_Created < '2018-01-01')
  
  group by DL_StandardLetter

  order by [DL_StandardLetter] asc

现在它正在返回DL_StandardLetterStatistics日期早于 2018 年的所有记录，并将它们按DL_StandardLetter.

以下面的数据为例。（大约有200000条记录）

| DL_Created | DL_StandardLetter |
 --------------------------------
| 2016-06-06 | 2352              |
| 2019-04-06 | 2352              |
| 2016-06-06 | 2542              |
| 2012-01-01 | 302               |
| 2012-01-01 | 2542              |
| 2017-02-08 | 302               |

我只希望结果返回2542 and 302，因为所有结果都在 2018 年之前。谁能指出我做错了什么？

标签： sql-server

Answer 1

原因是，您在 WHERE 子句中有 OR 条件。因此，由于其他 OR 条件，数据正在流动。似乎存在一些数据质量问题，其中 DL_Modified 比 DL_Created 更早。

where (DL_Modified < '2018-01-01' or DL_Created < '2018-01-01')

您可以有额外的 HAVING 子句，仅过滤年份 < 2018 的记录。

SELECT 
       MAX([DL_Id]) as [DL_Id]
      ,MAX([DL_CreatedBy]) as [DL_CreatedBy]
      ,MAX([DL_Created]) as [DL_Created]
      ,MAX([DL_ModifiedBy]) as [DL_ModifiedBy]
      ,MAX([DL_Modified]) as [DL_Modified]
      ,[DL_StandardLetter]
      ,count(1)
  FROM [DB].[dbo].[DL_StandardLetterStatistics]

  where (DL_Modified < '2018-01-01' or DL_Created < '2018-01-01')
  
  group by DL_StandardLetter
  HAVING YEAR(MAX([DL_CreatedBy])) < 2018
  ORDER BY [DL_StandardLetter] asc