无法通过Group By与Order By获取SQL Server Compact数据库最新账单记录

原查询存在的问题 #

1. TOP 1限制返回单条记录：你的需求是获取每个消费者的最新账单，但TOP 1仅会返回排序后的第一条数据，无法实现按消费者分组取最新记录的目标。
2. 排序逻辑错误：分开按DATEPART(Month, MM.BillMonth)和DATEPART(Year, MM.BillMonth)降序排序会导致逻辑混乱（比如2023年1月会排在2022年12月之后），直接对BillMonth日期字段降序排序即可。
3. 表名拼写错误：Cosumers应为Consumers。
4. 不必要的SUM：如果每个消费者每月只有一条账单记录，SUM(MM.AfterDuePayable)完全多余；如果每月有多条，需要先分组求和再取最新月份数据。

解决方案（分版本兼容） #

方案1：使用窗口函数（SQL Server Compact 4.0及以上版本） #

利用ROW_NUMBER()窗口函数按消费者分组，对账单日期降序编号，取每个组的第一条（编号=1）即可得到最新账单。

场景1：每个消费者每月仅一条账单

WITH RankedBills AS (
    SELECT 
        CM.id,
        MM.BillMonth,
        MM.AfterDuePayable,
        -- 按消费者分组，账单日期降序编号
        ROW_NUMBER() OVER(PARTITION BY CM.id ORDER BY MM.BillMonth DESC) AS rn
    FROM MonthlyBills MM
    -- 用INNER JOIN替代LEFT JOIN，因为WHERE条件已过滤未激活消费者，效率更高
    INNER JOIN Consumers CM ON MM.ConsumerCode = CM.id
    WHERE CM.isActive = 0 AND MM.AfterDuePayable > 0
)
SELECT id, BillMonth, AfterDuePayable
FROM RankedBills
WHERE rn = 1
ORDER BY id;

场景2：每个消费者每月有多条账单，需求和

如果同一消费者同一月份有多条账单，需要先按消费者+月份分组求和，再取最新月份的总和：

WITH SummedBills AS (
    SELECT 
        CM.id,
        MM.BillMonth,
        SUM(MM.AfterDuePayable) AS AfterDuePayable
    FROM MonthlyBills MM
    INNER JOIN Consumers CM ON MM.ConsumerCode = CM.id
    WHERE CM.isActive = 0 AND MM.AfterDuePayable > 0
    GROUP BY CM.id, MM.BillMonth
),
RankedBills AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY BillMonth DESC) AS rn
    FROM SummedBills
)
SELECT id, BillMonth, AfterDuePayable
FROM RankedBills
WHERE rn = 1
ORDER BY id;

方案2：关联子查询（兼容所有SQL Server Compact版本） #

如果你的数据库版本不支持窗口函数，可通过子查询先获取每个消费者的最新账单日期，再关联原表获取对应记录。

场景1：每个消费者每月仅一条账单

SELECT 
    CM.id,
    MM.BillMonth,
    MM.AfterDuePayable
FROM MonthlyBills MM
INNER JOIN Consumers CM ON MM.ConsumerCode = CM.id
-- 关联子查询获取每个消费者的最新账单日期
INNER JOIN (
    SELECT ConsumerCode, MAX(BillMonth) AS MaxBillMonth
    FROM MonthlyBills
    GROUP BY ConsumerCode
) AS Latest ON MM.ConsumerCode = Latest.ConsumerCode AND MM.BillMonth = Latest.MaxBillMonth
WHERE CM.isActive = 0 AND MM.AfterDuePayable > 0
ORDER BY id;

场景2：每个消费者每月有多条账单，需求和

SELECT 
    S.id,
    S.BillMonth,
    S.AfterDuePayable
FROM (
    SELECT 
        CM.id,
        MM.BillMonth,
        SUM(MM.AfterDuePayable) AS AfterDuePayable
    FROM MonthlyBills MM
    INNER JOIN Consumers CM ON MM.ConsumerCode = CM.id
    WHERE CM.isActive = 0 AND MM.AfterDuePayable > 0
    GROUP BY CM.id, MM.BillMonth
) AS S
INNER JOIN (
    SELECT id, MAX(BillMonth) AS MaxBillMonth
    FROM (
        SELECT CM.id, MM.BillMonth
        FROM MonthlyBills MM
        INNER JOIN Consumers CM ON MM.ConsumerCode = CM.id
        WHERE CM.isActive = 0 AND MM.AfterDuePayable > 0
        GROUP BY CM.id, MM.BillMonth
    ) AS Grouped
    GROUP BY id
) AS Latest ON S.id = Latest.id AND S.BillMonth = Latest.MaxBillMonth
ORDER BY S.id;

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