每个项目选择最大值记录的SQL SERVER查询

Question

这是样例表格

我需要实现的是仅获取或显示具有最高月份值的租户记录。如果每个月相等，则需要基于最新日期值。以下是示例所需输出。

使用max函数并结合临时表编写了此代码，但未能获得所需结果。

select tenant, name,  date, month
into #sample
from tenant


select * 
from  #sample  
where months = (select max(months)from #sample)

我认为，代码正在获取整个列表中的最大值，而不考虑每个租户的过滤。

并将其输出为类似于这样的内容。

任何帮助将不胜感激 :)

- rickyProgrammer

Answer 1

这可以通过使用row_number窗口函数来完成：

select tenant, name, date, months
  from (select t.*,
               row_number() over (partition by t.tenant, t.name order by t.months desc, t.date desc) as rn
          from TableName t) x
 where rn = 1

- sstan

---

这个可行。我可以问一下，rn 是否总是返回 1？ - rickyProgrammer

对于每一组不同的 tenant/name（使用 partition by 子句），row_number 函数将按顺序对行进行编号，从 1,2,3,4,... 开始，始终以 1 开始，使用 order by 子句来确定顺序。因此，由于 order by 子句设置为 t.months desc, t.date desc，对于每个 tenant/name 组合，具有最大 months 值（如果有多个行具有相同的 months 值，则具有最新的 date）的行将具有 rn 值为 1。 - sstan

为了更好地理解这一点，请尝试单独运行子查询，以便您可以查看所有行的“rn”值。 - sstan

Answer 2

你可以使用row_number函数。 查询

;with cte as 
(
    select rn = row_number() over 
    (
        partition by tenant
        order by months desc,[date] desc
    ),*
    from table_name
)
select tenant,name,[date],months from cte
where rn = 1;

- Ullas

---

使用公共表达式（CTE）是否比子查询（如其他答案中所做的）有优势？ - pseudocoder

CTE可以递归使用；子查询则不行。这使得它们特别适合树形结构。 - A_Sk

@Ajmot 好的，谢谢。但是为了明确起见，这个特定应用程序中没有递归的必要，对吗？ - pseudocoder

另一个不同点是，CTE可以作为不同的表替代物使用多次，而子查询只能使用一次。 - t-clausen.dk

@t-clausen.dk 好的，我认为在这个例子中我们也不需要那个功能...那就是我的意思。我想我的问题应该更清楚一些：在这种特定情况下，使用CTE是否有任何理由优于子查询呢？ - pseudocoder

在这个例子中，我没有看到使用CTE在逻辑或执行方面有任何改进; 然而，依我的看法，CTE比内联视图更容易阅读，因此我通常更喜欢使用CTE。 - Michael L.