sql - 在另一列中选择具有 MAX 的列子集上的 DISTINCT 记录

Question

几个小时以来，我一直在网站上查看其他 T-SQL 问题，包括 DISTINCT 和 MAX，但找不到完全符合我需要的问题。这是我的数据集和查询目标的描述。非常感谢任何指导。

数据集数据集是上一个计费周期的客户、客户站点、日期和值的列表，包含以下列。单个客户可以拥有多个站点:

客户、站点、日期、计数器、CounterValue、CollectorNode

查询要求对于给定的计费周期，我想选择以下内容

• DISTINCT(客户和网站)
• 每个 DISTINCT 客户和站点的此计费周期的 MAX(CounterValue)
• 同时仍然从表中返回该记录的所有字段(CollectorNode、Date、Counter)

我在这里面临的挑战是我无法在为每个列选择 DISTINCT 列和 MAX 时返回所有列。我进行了许多不同的尝试，为每个客户/站点组合返回了多条记录。

Answer 1

使用自连接:

SELECT ds.customer,
       ds.site,
       ds.counter, 
       ds.countervalue,
       ds.collectornode
  FROM DATASET ds
  JOIN (SELECT t.customer,
               t.site,
               MAX(t.countervalue) AS max_countervalue
          FROM DATASET t
      GROUP BY t.customer, t.site) x ON x.customer = ds.customer
                                    AND x.site = ds.site
                                    AND x.max_countervalue = ds.countervalue

使用 CTE 和 ROW_NUMBER(SQL Server 2005+):

WITH example AS (
   SELECT ds.customer,
          ds.site,
          ds.counter, 
          ds.countervalue,
          ds.collectornode,
          ROW_NUMBER() OVER(PARTITION BY ds.customer, ds.site
                                ORDER BY ds.countervalue DESC) AS rank
     FROM DATASET ds)
SELECT e.customer,
       e.site,
       e.counter, 
       e.countervalue,
       e.collectornode
  FROM example e
 WHERE e.rank = 1