SQL Server BETWEEN

Question

我有一张表格，其中包含年、月和几列数字

Year   Month  Total
2011     10    100
2011     11    150
2011     12    100  
2012     01    50
2012     02    200

现在，我想选择2011年11月至2012年2月之间的行。请注意，我希望查询使用范围，就像表中有一个日期列一样。

- user173552

---

如果您想使用范围，必须选择一个计算列（如concat(year, month)），这对性能不利。最好分别在两个列上使用查询（尽管相对复杂），因为这样可以使用索引。 - Thilo

Answer 1

想出一种使用BETWEEN与现有表格一起使用的方法是可行的，但在任何情况下都会导致更差的性能：

• 最好情况下，它将消耗更多的CPU来对行进行某种计算，而不是将其作为日期处理。
• 最坏的情况下，它将强制对表中的每一行进行表扫描，但如果您的列具有索引，则可以通过正确的查询进行查找。这可能是一个巨大的性能差异，因为将约束条件强制到BETWEEN子句中将禁用使用索引。

如果您的日期列上有索引并且关心性能，请改用以下建议：

DECLARE
   @FromDate date = '20111101',
   @ToDate date = '20120201';

SELECT *
FROM dbo.YourTable T
WHERE
   (
      T.[Year] > Year(@FromDate)
      OR (    
         T.[Year] = Year(@FromDate)
         AND T.[Month] >= Month(@FromDate)
      )
   ) AND (
      T.[Year] < Year(@ToDate)
      OR (
         T.[Year] = Year(@ToDate)
         AND T.[Month] <= Month(@ToDate)
      )
   );

然而，可以理解您不想使用这样的结构，因为它非常笨拙。因此，这里有一个妥协查询，至少使用数字计算，并且将使用比日期转换为字符串计算少得多的 CPU（虽然不足以弥补强制扫描这个真正的性能问题）。

SELECT *
FROM dbo.YourTable T
WHERE
   T.[Year] * 100 + T.[Month] BETWEEN 201111 AND 201202;

如果你在Year上建了索引，可以通过以下查询语句获得很大的提升，有机会进行索引扫描:

SELECT *
FROM dbo.YourTable T
WHERE
   T.[Year] * 100 + T.[Month] BETWEEN 201111 AND 201202
   AND T.[Year] BETWEEN 2011 AND 2012; -- allows use of an index on [Year]

虽然这违反了您使用单个BETWEEN表达式的要求，但它并不会太痛苦，并且在Year索引方面表现非常好。

您还可以更改表格。坦白地说，使用单独的数字代替具有日期数据类型的单个列是不好的。原因是正如您现在面临的确切问题一样--很难查询。

在某些数据仓库场景中，如果节省字节很重要，我可以想象出存储日期作为数字（例如201111）的情况，但这并不推荐。最好的解决方案是更改表格以使用日期，而不是拆分月份和年份的数值。只需存储该月的第一天，并认识到它代表整个月。

如果更改使用这些列的方式不是选项，但仍然可以更改表格，则可以添加一个持久计算列：

ALTER Table dbo.YourTable
   ADD ActualDate AS (DateAdd(year, [Year] - 1900, DateAdd(month, [Month], '18991201')))
   PERSISTED;

使用这个方法，你只需要执行以下操作：

SELECT *
FROM dbo.YourTable
WHERE
   ActualDate BETWEEN '20111101' AND '20120201';

< p > PERSISTED 关键字意味着，虽然您仍将获得扫描，但它不必对每行进行任何计算，因为表达式在每个 INSERT 或 UPDATE 时计算并存储在行中。但是，如果在此列上添加索引，则可以获得查找，这将使其表现非常好（尽管总体而言，这仍不如更改使用实际日期列理想，因为它将占用更多空间并影响INSERT和UPDATE）。

CREATE NONCLUSTERED INDEX IX_YourTable_ActualDate ON dbo.YourTable (ActualDate);

总结：如果你真的无法以任何方式更改表格，那么你必须在某种程度上做出妥协。当日期被拆分成单独的列存储时，要想获得简单的语法和良好的性能是不可能的。

- ErikE

Answer 2

(Year > @FromYear OR Year = @FromYear AND Month >= @FromMonth)
AND (Year < @ToYear OR Year = @ToYear AND Month <= @ToMonth)

- Dr. Wily's Apprentice

---

我想指出这与我的答案中的第一个查询在功能上是相同的。您在其中有6个条件和5个连接词。我的第一个查询具有完全相同的6个条件和5个连接词，只是稍微重新排列了一下。 - ErikE

在我看来，ErikE的版本更易读。特别是，你不必知道AND或OR哪个绑定更强。 - Thilo

@ErikE 对不起，我在发布答案时应该更加小心。我看到你的答案通常使用相同的方法，另外还有一些其他好的建议。但请考虑修改你的查询，因为在@FromDate和@ToDate在同一年的情况下，它无法正常工作。如果你解决了这个问题，那么我会删除我的答案，因为它没有提供太多信息。这种表格设置（将年份和月份作为单独的列）在过去曾经让我犯过错误。 - Dr. Wily's Apprentice

很好的发现！你对我的条件完全正确。我会尽快修复它。 - ErikE

Answer 3

您的示例表似乎表明每年每月只有一条记录（如果它确实是按月汇总的表）。如果是这样，即使在数十年的活动中，该表中也很少会积累数据。连接表达式解决方案将起作用，并且性能（在这种情况下）不会成为问题：

SELECT * FROM Table WHERE ((Year * 100) + Month) BETWEEN 201111 AND 201202

如果不是这种情况，而且您的表中确实有大量记录（超过几千条），那么您有几个选择：

1. 更改您的表格，将年份和月份以YYYYMM的格式存储（可以是整数值或文本）。此列可以替换您当前的年份和索引列，也可以作为它们的附加列（尽管这会破坏正常形式）。对此列进行索引并针对其进行查询。

2. 创建一个单独的表格，每年每月一个记录，并按上述方式添加可索引列。在查询中，将此表格与源表格连接并针对较小表格中的索引列执行查询。

- Larry Lustig

---

我认为#2没有意义——为什么要创建一个单独的表格？！？#1和你提供的查询是其他答案中已经提供的信息的副本。 - ErikE

创建一个单独的表可以存储可索引值，而无需在数据表中重复数万行（如果存在这么多行）。这显着减少了维护索引值的计算负载，规范化了索引值和它们映射到的年月对之间的关系，并且可以在原始表上不需要任何权限的情况下实现。但是，它确实引入了额外的JOIN，这就是为什么我首先提到了较少规范化形式的原因。 - Larry Lustig

我明白你的意思，Larry。现在你强调一对多的关系，这确实有道理。 - ErikE