如何用SQL查询澳大利亚财年的最近4个财季及间隔季度数？

我来帮你简化这个查询！针对澳大利亚财季规则（7月至次年6月，财年Y对应Y-1年7月到Y年6月，财季ID格式为YYYYQ），我们可以用更简洁的逻辑生成最近4个已结束财季的结果，避免繁琐的重复判断。

核心思路 #

先把当前日期转换为一个连续的财季数字（把每个财季当成一个递增的序号），这样倒推前几个财季时直接减1即可，不用处理字符串拼接的跨财年麻烦。然后生成1到4的间隔数，再把每个序号转换回YYYYQ格式。

简洁实现代码 #

DECLARE @CurrentDate DATE = '2018-03-28'; -- 替换成你的目标日期

-- 步骤1：计算当前日期所在财季的连续序号（比如2018年3月对应财季20183，序号为8071）
DECLARE @CurrentFiscalQuarterNum INT;
SET @CurrentFiscalQuarterNum = 
    -- 计算财年对应的基准数：(财年-1)*4
    (CASE WHEN MONTH(@CurrentDate) >= 7 THEN YEAR(@CurrentDate) + 1 ELSE YEAR(@CurrentDate) END - 1) * 4 +
    -- 计算财季在财年中的位置（1-4）
    CASE 
        WHEN MONTH(@CurrentDate) BETWEEN 7 AND 9 THEN 1
        WHEN MONTH(@CurrentDate) BETWEEN 10 AND 12 THEN 2
        WHEN MONTH(@CurrentDate) BETWEEN 1 AND 3 THEN 3
        WHEN MONTH(@CurrentDate) BETWEEN 4 AND 6 THEN 4
    END;

-- 步骤2：生成最近4个已结束财季，计算ID和间隔数
SELECT 
    -- 将连续序号转换回YYYYQ格式
    CAST(((@CurrentFiscalQuarterNum - QuartersAgo) - 1) / 4 + 1 AS VARCHAR(4)) + 
    CAST(((@CurrentFiscalQuarterNum - QuartersAgo) - 1) % 4 + 1 AS VARCHAR(1)) AS ID,
    QuartersAgo
FROM (VALUES (1), (2), (3), (4)) AS q(QuartersAgo)
ORDER BY QuartersAgo;

测试结果（当@CurrentDate='2018-03-28'时） #

另一种递归CTE实现（无需变量） #

如果偏好不用变量的写法，可以用递归CTE生成财季序列：

DECLARE @CurrentDate DATE = '2018-03-28';

WITH RecentFiscalQuarters AS (
    -- 起始：最近已结束的财季
    SELECT 
        CAST(
            CASE 
                WHEN MONTH(@CurrentDate) BETWEEN 1 AND 3 THEN CAST(YEAR(@CurrentDate) AS VARCHAR(4)) + '2'
                WHEN MONTH(@CurrentDate) BETWEEN 4 AND 6 THEN CAST(YEAR(@CurrentDate) AS VARCHAR(4)) + '3'
                WHEN MONTH(@CurrentDate) BETWEEN 7 AND 9 THEN CAST(YEAR(@CurrentDate)+1 AS VARCHAR(4)) + '4'
                WHEN MONTH(@CurrentDate) BETWEEN 10 AND 12 THEN CAST(YEAR(@CurrentDate)+1 AS VARCHAR(4)) + '1'
            END AS VARCHAR(5)) AS ID,
        1 AS QuartersAgo
    UNION ALL
    -- 递归生成前3个财季
    SELECT 
        CAST(
            CASE RIGHT(q.ID, 1)
                WHEN '1' THEN CAST(LEFT(q.ID, 4) AS INT) - 1 || '4'
                ELSE LEFT(q.ID, 4) || CAST(RIGHT(q.ID, 1) AS INT) - 1
            END AS VARCHAR(5)) AS ID,
        q.QuartersAgo + 1 AS QuartersAgo
    FROM RecentFiscalQuarters q
    WHERE q.QuartersAgo < 4
)
SELECT ID, QuartersAgo
FROM RecentFiscalQuarters
ORDER BY QuartersAgo;

这两种写法都比繁琐的重复查询简洁很多，而且逻辑清晰，容易维护。

内容的提问来源于stack exchange，提问作者Jay Killeen