按部分列 DISTINCT 并获取另一列的 MAX 记录

结论

可以使用窗口函数 ROW_NUMBER()：先按 Customer 和 Site 分组，再按 CounterValue 从大到小为组内记录编号，最后保留编号为 1 的记录。

查询返回的是 CounterValue 最大值所在的完整记录，因此还能同时取得 CollectorNode、Date、Counter 等字段。

WITH RankedRecords AS
(
    SELECT
        Customer,
        Site,
        [Date],
        [Counter],
        CounterValue,
        CollectorNode,
        ROW_NUMBER() OVER
        (
            PARTITION BY Customer, Site
            ORDER BY CounterValue DESC, [Date] DESC
        ) AS RowNum
    FROM dbo.YourTable
    WHERE [Date] >= @BillingCycleStart
      AND [Date] <  @BillingCycleEnd
)
SELECT
    Customer,
    Site,
    [Date],
    [Counter],
    CounterValue,
    CollectorNode
FROM RankedRecords
WHERE RowNum = 1;

为什么 DISTINCT 和 MAX() 不能直接解决

MAX(CounterValue) 只能找出每组的最大值，无法确定其他字段应该取自哪条记录。

例如，同一个客户站点中有以下数据：

下面的聚合查询可以得到最大值 150：

SELECT
    Customer,
    Site,
    MAX(CounterValue) AS MaxCounterValue
FROM dbo.YourTable
GROUP BY Customer, Site;

但如果把 Date、CollectorNode 等字段直接加入 GROUP BY：

GROUP BY Customer, Site, [Date], CollectorNode

分组粒度就变了。数据不再以每个 Customer、Site 组合为一组，而是以这些字段的每种组合为一组，因此每个站点仍可能返回多条记录。

DISTINCT 只能删除所有已选字段都完全相同的重复行，不能表达“为每个客户站点选出最大值所在的记录”。

使用 ROW_NUMBER() 选择完整记录

窗口函数不会把多行聚合成一行，而是为每条记录计算组内排名：

ROW_NUMBER() OVER
(
    PARTITION BY Customer, Site
    ORDER BY CounterValue DESC
)

这里：

• PARTITION BY Customer, Site 按客户和站点的组合分别排名。
• ORDER BY CounterValue DESC 将最大值排在最前面。
• RowNum = 1 只保留每组排名第一的完整记录。

计费周期条件应在排名前应用。否则，查询可能先从全部历史记录中找出最大值，再检查该记录是否处于指定周期内，得到的结果会与需求不符。

如果结束参数表示下一个计费周期的起点，建议使用左闭右开的时间范围：

WHERE [Date] >= @BillingCycleStart
  AND [Date] <  @BillingCycleEnd

这种写法适用于包含时间部分的 datetime 或 datetime2 字段，也不需要手动拼接 23:59:59。

最大值相同时如何处理

如果同一个 Customer、Site 组合中有多条记录的 CounterValue 都是最大值，只按该字段排序时，ROW_NUMBER() 会选出其中一条，但无法保证具体选中哪条。

如果业务规则要求在最大值相同时选择日期最新的记录，可以增加次级排序：

ROW_NUMBER() OVER
(
    PARTITION BY Customer, Site
    ORDER BY CounterValue DESC, [Date] DESC
)

如果日期也可能相同，最好再加入唯一键，例如 RecordId：

ROW_NUMBER() OVER
(
    PARTITION BY Customer, Site
    ORDER BY CounterValue DESC, [Date] DESC, RecordId DESC
)

排序字段只有能够唯一确定记录顺序，查询结果才会稳定。

如果需要返回所有并列的最大值记录，可以改用 RANK()：

WITH RankedRecords AS
(
    SELECT
        Customer,
        Site,
        [Date],
        [Counter],
        CounterValue,
        CollectorNode,
        RANK() OVER
        (
            PARTITION BY Customer, Site
            ORDER BY CounterValue DESC
        ) AS ValueRank
    FROM dbo.YourTable
    WHERE [Date] >= @BillingCycleStart
      AND [Date] <  @BillingCycleEnd
)
SELECT
    Customer,
    Site,
    [Date],
    [Counter],
    CounterValue,
    CollectorNode
FROM RankedRecords
WHERE ValueRank = 1;

此时每个客户站点可能返回多行，这是存在并列最大值时的正常结果。

使用聚合查询再关联原表

另一种方法是先计算每组的最大值，再关联回原表：

WITH MaximumValues AS
(
    SELECT
        Customer,
        Site,
        MAX(CounterValue) AS MaxCounterValue
    FROM dbo.YourTable
    WHERE [Date] >= @BillingCycleStart
      AND [Date] <  @BillingCycleEnd
    GROUP BY Customer, Site
)
SELECT
    t.Customer,
    t.Site,
    t.[Date],
    t.[Counter],
    t.CounterValue,
    t.CollectorNode
FROM dbo.YourTable AS t
INNER JOIN MaximumValues AS m
    ON m.Customer = t.Customer
   AND m.Site = t.Site
   AND m.MaxCounterValue = t.CounterValue
WHERE t.[Date] >= @BillingCycleStart
  AND t.[Date] <  @BillingCycleEnd;

这种写法会返回所有并列的最大值记录。如果要求每组严格返回一行，还需要定义并列时的取舍规则，所以使用 ROW_NUMBER() 通常更直接。

注意事项

• 建议用方括号引用 [Date] 和 [Counter]，避免它们与 SQL 关键字或类型名称产生歧义。更好的做法是改用含义明确的列名。
• MAX() 会忽略 NULL。使用 ORDER BY CounterValue DESC 时，还需要确认 CounterValue 全为 NULL 的分组是否应该返回记录。
• 如果 CounterValue 使用字符类型保存，数据库可能按字符串规则排序。例如，字符串 '9' 可能排在 '100' 前面。需要进行数值比较的字段应使用合适的数值类型。
• 数据量较大时，可以结合实际查询情况，考虑建立以计费周期、Customer、Site 为主要键并包含 CounterValue 的索引。具体的索引顺序应根据执行计划和现有索引确定。
• 此处不需要同时使用 DISTINCT 和 GROUP BY。窗口函数已经按 Customer、Site 划分数据，并通过排名选出了每组所需的记录。

备注：内容仅供参考。