sql - 按日期计算前 5 个值

Question

我有一个表defect_description和productionday字段如下：

+--------------------+---------------+
| defect_description | productionday |
+--------------------+---------------+
| BLOWN GASKET       | 2020-01-01    |
| BLOWN GASKET       | 2020-01-01    |
| BLOWN GASKET       | 2020-01-01    |
| BLOWN TIRE         | 2020-01-01    |
| BLOWN LIGHT        | 2020-01-01    |
| BLOWN LIGHT        | 2020-01-01    |
| BLOWN LIGHT        | 2020-01-01    |
| BLOWN LIGHT        | 2020-01-01    |
| BLOWN TIRE         | 2020-02-01    |
| BLOWN TIRE         | 2020-02-01    |
| BLOWN TIRE         | 2020-02-01    |
+--------------------+---------------+

我如何计算每天的前 5 个缺陷：

+---------------+--------------------+-------+
| productionday | defect_description | count |
+---------------+--------------------+-------+
| 2020-01-01    | BLOWN LIGHT        |     4 |
| 2020-01-01    | BLOWN GASKET       |     3 |
| 2020-01-01    | BLOWN TIRE         |     1 |
| 2020-02-01    | BLOWN TIRE         |     3 |
+---------------+--------------------+-------+

标签： sqlsql-server

Answer 1

我认为这可以满足您的要求：

select t.*
from (select defect_description, productionday, count(*) as c,
             row_number() over (partition by productionday order by count(*) desc) as seqnum
      from tab
      group by productionday, defect_description
     ) t
where seqnum <= 5