sql，选择特定日期的前 5 个事件

Question

我的 sql server 上有一个包含诊断代码的表。在几天内，一些诊断被设置并推送到表中。

表格(诊断)如下所示:诊断、诊断代码、日期。

我试图用 sql 输出的是:在某些天(从 X 天到现在)出现次数最多的前 5 个诊断代码如下所示:

10 sept 2015 : 
100 times 1157 (diagnosecode)
90 times 11337 (diagnosecode)
80 times 111237 (diagnosecode)
70 times 112312357 (diagnosecode)
60 times 1112317 (diagnosecode)

9 sept 2015 : 
100 times 1157 (diagnosecode)
90 times 11337 (diagnosecode)
80 times 111237 (diagnosecode)
70 times 112312357 (diagnosecode)
60 times 1112317 (diagnosecode)

8 sept 2015 : 
100 times 1157 (diagnosecode)
90 times 11337 (diagnosecode)
80 times 111237 (diagnosecode)
70 times 112312357 (diagnosecode)
60 times 1112317 (diagnosecode)

我试过这个:

SELECT TOP(5) count([diagnosecode]) as occurences, diagnosecode,     
FROM diagnoses 
WHERE date = SYSDATETIME()
GROUP BY diagnosecode 
ORDER BY date DESC, COUNT([diagnosecode]) DESC

它只适用于今天。我不知道如何使查询工作多天。我将如何处理这个问题？

Answer 1

如果你想要每天五个，然后使用窗口函数:

SELECT date, occurrences, diagnosecode,     
FROM (SELECT diagnosecode, date, COUNT(*) as occurrences,
             ROW_NUMBER() OVER (PARTITION BY date ORDER BY COUNT(*) DESC) as seqnum
     FROM diagnoses 
     GROUP BY diagnoses, date
    ) dd
WHERE seqnum <= 5
ORDER BY date DESC, occurrences DESC;

注意:如果日期确实有时间部分，那么您需要将其转换为 date 以仅提取日期部分。