SQL 如何创建 where 子句以使用此格式 YYYYMM 过滤最后六个周期?

Question

我想运行一个查询，该查询使用以下周期格式YYYYMM对前 6 个周期求和。

SELECT sum(t.usage)
FROM t
WHERE t.peroid ?????
GROUP BY t.period
Period   Usage
-------------
201907   30
201908   40
201909   50
201910   60
201911   70
201912   60
202001   20

Answer 1

试试这个：

select  t.period        as  "Period",
sum(t.usage)    as  "Usage"
from    t
where   t.peroid >= cast(convert(char(6),dateadd(month, -6, cast(convert(char(6),getdate(),112)+'01' as date)),112) as int)
group by t.period
order by t.period

Answer 2

似乎您可以使用OFFSET来实现这一点：

WITH CTE AS (
SELECT Usage
FROM dbo.t
ORDER BY t.Period DESC
OFFSET 0 ROWS FETCH NEXT 6 ROWS ONLY)
SELECT SUM(Usage)
FROM CTE;

Answer 3

您可以尝试以下查询。

DECLARE @startDate DATETIME = getdate()
SELECT sum(TotUsage)
FROM (
SELECT sum(t.usage) AS Totusage
FROM UsageDetails t
GROUP BY t.period
HAVING t.period BETWEEN Convert(VARCHAR(6), DATEADD(month, - 6, @startDate), 112)
AND Convert(VARCHAR(6), @startDate, 112)
AND t.period > Convert(VARCHAR(6), DATEADD(month, - 6, @startDate), 112)
) a

这是现场示例。