取得与该列的SQL Server 2008

问题描述：

Sample 取得与该列的SQL Server 2008

我想要做上述SQL Server 2008中的任何想法的MAX值整排？

你怎么要处理，其中最大值为两天共享几个月？ – geofftnz 2011-06-16 03:56:36

[SQL - 获取具有列的最大值的行]的可能重复（http://stackoverflow.com/questions/121387/sql-fetch-the-row-which-has-the-max-value- for-a-column） – 2011-06-16 03:57:55

答

是否这样？

设置：

declare @MyTable table(Year int, Month int, Day int, Total int) 

insert @MyTable 
values 
    (2005, 9, 23, 12), 
    (2005, 9, 26, 5), 
    (2005, 9, 24, 1), 
    (2005, 9, 15, 28), 
    (2005, 9, 21, 1), 
    (2005, 9, 13, 1), 
    (2005, 10, 31, 5), 
    (2005, 11, 18, 115), 
    (2005, 11, 20, 1), 
    (2005, 11, 11, 1), 
    (2005, 11, 19, 1)

查询：

;with cte 
as 
(
    select *, 
     row_number() over(partition by Year, Month order by Total desc) RowNumber 
    from @MyTable 
) 
select Year, Month, Day, Total 
from cte 
where RowNumber = 1

输出：

Year  Month  Day   Total 
----------- ----------- ----------- ----------- 
2005  9   15   28 
2005  10   31   5 
2005  11   18   115

完美！非常感谢！！你在6分钟内投票给你 – GayanSanjeewa 2011-06-16 04:05:28

取得与该列的SQL Server 2008

相关推荐