sql - 查询以从组中查找第一个和第二个最大值

Question

我有一个这样的查询:

SELECT
 DATEPART(year,some_date),
 DATEPART(month,some_date),
 MAX(some_value) max_value
FROM
 some_table
GROUP BY
    DATEPART(year,some_date),
    DATEPART(month,some_date)

这将返回一个表，其中包含:年、月、该月的最大值。
我想修改查询，以便我可以获得:
年，月，月的最大值， 本月第二大值 在每一行。
在我看来，像“TOP 2”、“NOT IN TOP 1”或子选择等众所周知的解决方案在这里不起作用。
(确切地说 - 我使用的是 SQL Server 2008。)

Answer 1

在我看来，这个问题需要一个查询，该查询将在每个月和每年的同一行中返回最佳和次佳，如下所示:

month, year, best, second best
...
...

而不是同一月份和年份的两行包含最佳值和次佳值。

这是我想出的解决方案，所以如果有人有更简单的方法来实现这一点，我想知道。

with ranks as (
    select 
        year(entrydate) as [year], 
        month(entrydate) as [month], 
        views, 
        rank() over (partition by year(entrydate), month(entrydate) order by views desc) as [rank]
    from product
)
select 
    t1.year, 
    t1.month, 
    t1.views as [best], 
    t2.views as [second best]
from ranks t1
    inner join ranks t2
        on t1.year = t2.year
        and t1.month = t2.month
        and t1.rank = 1
        and t2.rank = 2

编辑:出于好奇，我进行了更多测试，最终在 Stephanie Page's answer 上得到了一个更简单的变体。不使用附加子查询。我将 rank() 函数更改为 row_number() ，因为当两个最大值相同时它不起作用。

with ranks as (
    select 
        year(entrydate) as [year], 
        month(entrydate) as [month], 
        views, 
        row_number() over (partition by year(entrydate), month(entrydate) order by views desc) as [rank]
    from product
)
select 
    t1.year, 
    t1.month, 
    max(case when t1.rank = 1 then t1.views else 0 end) as [best], 
    max(case when t1.rank = 2 then t1.views else 0 end) as [second best]
from 
    ranks t1
where
    t1.rank in (1,2)
group by
    t1.year, t1.month