我有一张表格,其中包含年、月和几列数字
Year Month Total
2011 10 100
2011 11 150
2011 12 100
2012 01 50
2012 02 200
现在,我想选择2011年11月至2012年2月之间的行。请注意,我希望查询使用范围,就像表中有一个日期列一样。
我有一张表格,其中包含年、月和几列数字
Year Month Total
2011 10 100
2011 11 150
2011 12 100
2012 01 50
2012 02 200
现在,我想选择2011年11月至2012年2月之间的行。请注意,我希望查询使用范围,就像表中有一个日期列一样。
想出一种使用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)
)
);
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);
总结:如果你真的无法以任何方式更改表格,那么你必须在某种程度上做出妥协。当日期被拆分成单独的列存储时,要想获得简单的语法和良好的性能是不可能的。
(Year > @FromYear OR Year = @FromYear AND Month >= @FromMonth)
AND (Year < @ToYear OR Year = @ToYear AND Month <= @ToMonth)
@FromDate
和@ToDate
在同一年的情况下,它无法正常工作。如果你解决了这个问题,那么我会删除我的答案,因为它没有提供太多信息。这种表格设置(将年份和月份作为单独的列)在过去曾经让我犯过错误。 - Dr. Wily's Apprentice您的示例表似乎表明每年每月只有一条记录(如果它确实是按月汇总的表)。如果是这样,即使在数十年的活动中,该表中也很少会积累数据。连接表达式解决方案将起作用,并且性能(在这种情况下)不会成为问题:
SELECT * FROM Table WHERE ((Year * 100) + Month) BETWEEN 201111 AND 201202
如果不是这种情况,而且您的表中确实有大量记录(超过几千条),那么您有几个选择:
更改您的表格,将年份和月份以YYYYMM的格式存储(可以是整数值或文本)。此列可以替换您当前的年份和索引列,也可以作为它们的附加列(尽管这会破坏正常形式)。对此列进行索引并针对其进行查询。
创建一个单独的表格,每年每月一个记录,并按上述方式添加可索引列。在查询中,将此表格与源表格连接并针对较小表格中的索引列执行查询。