如何提取每年的首个和最后一个事件？SQL实现求助

原代码存在的问题 #

• 第一个CTE yearnum 的GROUP BY包含了eventid和EventDate，这会让每一条原始事件记录单独成为一个分组，MIN(eventdate)没有实际作用（每个组只有一个日期）。
• 第二个CTE rownumS 中，ROW_NUMBER()的排序条件是年份（YR ASC），无法区分同一年份内事件的先后顺序，自然无法定位到首尾事件。

解决方案一：使用行号筛选首尾事件 #

这种方法通过给同一年份的事件分别按日期升序、降序生成行号，筛选行号为1的记录即可得到首尾事件：

WITH ranked_events AS (
    SELECT
        eventid,
        eventdate,
        DATEPART(year, eventdate) AS event_year,
        EventName,
        EventDetails,
        CategoryID,
        CountryID,
        -- 升序行号：1为当年第一个事件
        ROW_NUMBER() OVER (PARTITION BY DATEPART(year, eventdate) ORDER BY eventdate ASC) AS rn_asc,
        -- 降序行号：1为当年最后一个事件
        ROW_NUMBER() OVER (PARTITION BY DATEPART(year, eventdate) ORDER BY eventdate DESC) AS rn_desc
    FROM tblevent
)
SELECT
    eventid,
    eventdate,
    event_year,
    EventName,
    EventDetails,
    CategoryID,
    CountryID
FROM ranked_events
WHERE rn_asc = 1 OR rn_desc = 1
-- 去重：避免全年仅一个事件时重复输出
GROUP BY eventid, eventdate, event_year, EventName, EventDetails, CategoryID, CountryID
ORDER BY event_year, eventdate;

解决方案二：通过日期边界关联筛选 #

先计算每个年份的最早、最晚事件日期，再关联原表筛选对应日期的事件：

WITH year_boundaries AS (
    SELECT
        DATEPART(year, eventdate) AS event_year,
        MIN(eventdate) AS first_event_date,
        MAX(eventdate) AS last_event_date
    FROM tblevent
    GROUP BY DATEPART(year, eventdate)
)
SELECT
    t.eventid,
    t.eventdate,
    y.event_year,
    t.EventName,
    t.EventDetails,
    t.CategoryID,
    t.CountryID
FROM tblevent t
JOIN year_boundaries y 
    ON DATEPART(year, t.eventdate) = y.event_year
    AND (t.eventdate = y.first_event_date OR t.eventdate = y.last_event_date)
ORDER BY y.event_year, t.eventdate;

两种方案说明 #

• 方案一：如果同一日期有多个事件，只会返回其中一个（因为ROW_NUMBER()会给同日期事件分配不同行号）；如果需要返回同日期的所有首尾事件，可把ROW_NUMBER()换成RANK()。
• 方案二：会返回所有在当年最早/最晚日期发生的事件，适合需要保留同日期所有首尾事件的场景。

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