如何用单条SQL查询获取每个会员每年的期初与期末余额

方案1：ROW_NUMBER + 条件聚合（兼容性最优） #

这种方法仅需一次表扫描，在子查询中同时计算升序、降序行号，再通过条件聚合提取每年的期初（最早记录）和期末（最晚记录）余额，几乎兼容所有支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等）。

SELECT
    YEAR(DateCol) AS YEAR,
    MemberID AS MEMBERID,
    MAX(CASE WHEN seqnum_asc = 1 THEN Amount END) AS STARTBALANCE,
    MAX(CASE WHEN seqnum_desc = 1 THEN Amount END) AS ENDBALANCE
FROM (
    SELECT
        MemberID,
        DateCol,
        Amount,
        -- 按日期升序排，标记每年第一条记录（期初）
        ROW_NUMBER() OVER (PARTITION BY MemberID, YEAR(DateCol) ORDER BY DateCol) AS seqnum_asc,
        -- 按日期降序排，标记每年第一条记录（期末）
        ROW_NUMBER() OVER (PARTITION BY MemberID, YEAR(DateCol) ORDER BY DateCol DESC) AS seqnum_desc
    FROM Tablet
) t
WHERE seqnum_asc = 1 OR seqnum_desc = 1
GROUP BY YEAR(DateCol), MemberID
-- 如需筛选特定会员，添加 WHERE MemberID = '1000009'（可放在子查询或外层）

方案2：FIRST_VALUE/LAST_VALUE + DISTINCT（代码最简洁） #

利用窗口函数直接提取分组内的第一条和最后一条金额，再通过DISTINCT去重，代码更简洁，但需注意LAST_VALUE的窗口范围设置（默认窗口仅包含当前行及之前的数据，必须显式指定覆盖整组）。

SELECT DISTINCT
    YEAR(DateCol) AS YEAR,
    MemberID AS MEMBERID,
    -- 取每年最早日期的金额（期初）
    FIRST_VALUE(Amount) OVER (
        PARTITION BY MemberID, YEAR(DateCol) 
        ORDER BY DateCol
    ) AS STARTBALANCE,
    -- 取每年最晚日期的金额（期末），指定窗口范围覆盖整组数据
    LAST_VALUE(Amount) OVER (
        PARTITION BY MemberID, YEAR(DateCol) 
        ORDER BY DateCol 
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS ENDBALANCE
FROM Tablet
-- 如需筛选特定会员，添加 WHERE MemberID = '1000009'

注意事项：处理同一日期多条记录的情况 #

如果同一会员在同一天有多条金额记录，建议先按MemberID和DateCol聚合金额（比如求和、取平均），再计算期初期末，避免随机取数：

SELECT
    YEAR(DateCol) AS YEAR,
    MemberID AS MEMBERID,
    MAX(CASE WHEN seqnum_asc = 1 THEN TotalAmount END) AS STARTBALANCE,
    MAX(CASE WHEN seqnum_desc = 1 THEN TotalAmount END) AS ENDBALANCE
FROM (
    SELECT
        MemberID,
        DateCol,
        SUM(Amount) AS TotalAmount, -- 按日期聚合金额，可根据需求改为AVG/MAX等
        ROW_NUMBER() OVER (PARTITION BY MemberID, YEAR(DateCol) ORDER BY DateCol) AS seqnum_asc,
        ROW_NUMBER() OVER (PARTITION BY MemberID, YEAR(DateCol) ORDER BY DateCol DESC) AS seqnum_desc
    FROM Tablet
    GROUP BY MemberID, DateCol
) t
WHERE seqnum_asc = 1 OR seqnum_desc = 1
GROUP BY YEAR(DateCol), MemberID

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