基于日期函数动态获取近三月Top10名称的T/SQL技术咨询

Alright, let's build this flexible solution so you don’t have to hardcode month values—it’ll automatically adjust based on the current date. Here’s how to tackle it step by step:

First, we need to reliably calculate the start and end dates for each target month (last month, two months ago, three months ago) using T-SQL date functions. This ensures we’re always grabbing complete months no matter when you run the query.

Step-by-Step Solution #

Let’s assume your table is named YourDataTable, with:

• Name: The column you want to pull Top 10 entries for
• RecordDate: The date column that links each record to a specific month
• PerformanceMetric: The numeric column you’re ranking by (e.g., sales, clicks, page views—swap this for your actual metric)

Here’s the full, maintainable query using CTEs:

WITH MonthRanges AS (
    -- Generate date boundaries for each target period
    SELECT
        'Last Month' AS PeriodLabel,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AS MonthStart,
        DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS MonthEnd
    UNION ALL
    SELECT
        'Two Months Ago' AS PeriodLabel,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0) AS MonthStart,
        DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)) AS MonthEnd
    UNION ALL
    SELECT
        'Three Months Ago' AS PeriodLabel,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0) AS MonthStart,
        DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0)) AS MonthEnd
),
RankedData AS (
    -- Rank names within each month period
    SELECT
        mr.PeriodLabel,
        ydt.Name,
        ydt.PerformanceMetric,
        ROW_NUMBER() OVER (PARTITION BY mr.PeriodLabel ORDER BY ydt.PerformanceMetric DESC) AS RankNum
    FROM YourDataTable ydt
    JOIN MonthRanges mr ON ydt.RecordDate BETWEEN mr.MonthStart AND mr.MonthEnd
)
-- Pull Top 10 for each period
SELECT
    PeriodLabel,
    Name,
    PerformanceMetric,
    RankNum
FROM RankedData
WHERE RankNum <= 10
ORDER BY PeriodLabel, RankNum;

Key Details Breakdown #

• MonthRanges CTE: This uses DATEDIFF(MONTH, 0, GETDATE()) to get the number of months since SQL Server’s epoch (1900-01-01), then adds/subtracts months to get exact month start/end dates. No more guessing about days in a month or edge cases like running the query mid-month!
• RankedData CTE: We join our main data to the date ranges, then use ROW_NUMBER() to rank names by your metric within each period. If you want to include ties (e.g., multiple names with the same top metric), swap ROW_NUMBER() for RANK().
• Final Select: Filters to only keep the top 10 ranks per period, ordered for easy reading.

Alternative (Simpler for Basic Use Cases) #

If you prefer a more straightforward approach without CTEs, use three separate SELECT TOP 10 queries combined with UNION ALL:

-- Last Month Top 10
SELECT
    'Last Month' AS PeriodLabel,
    Name,
    PerformanceMetric
FROM YourDataTable
WHERE RecordDate BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) 
                     AND DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
ORDER BY PerformanceMetric DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

UNION ALL

-- Two Months Ago Top 10
SELECT
    'Two Months Ago' AS PeriodLabel,
    Name,
    PerformanceMetric
FROM YourDataTable
WHERE RecordDate BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0) 
                     AND DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0))
ORDER BY PerformanceMetric DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

UNION ALL

-- Three Months Ago Top 10
SELECT
    'Three Months Ago' AS PeriodLabel,
    Name,
    PerformanceMetric
FROM YourDataTable
WHERE RecordDate BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0) 
                     AND DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0))
ORDER BY PerformanceMetric DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

This works great if you don’t need complex ranking logic, but the CTE approach is easier to update if you ever want to add more periods later.

Just remember to replace YourDataTable, RecordDate, and PerformanceMetric with your actual table and column names!

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