mysql - Mysql：如何找出日期范围内每个客户的第n次交货

Question

要求包括找出在特定日期范围内第 5 次交货的客户

我的数据看起来如下：

|ID|customer|sale_date  | amount|
=================================
|1  | 10   |   2021-02-09   |100|
|2  | 10   |   2021-02-10   |100|
|3  | 10   |   2021-02-11   |100|
|4  | 10   |   2021-02-12   |100|
|5  | 10   |   2021-02-13   |100|
|6  | 10   |   2021-02-14   |100|
|7  | 10   |   2021-02-15   |100|
|8  | 12   |   2021-02-09   |100|
|9  | 12   |   2021-02-10   |100|
|10 | 12   |   2021-02-11   |100|
|11 | 12   |   2021-02-12   |100|
|12 | 12   |   2021-02-13   |100|
|13 | 12   |   2021-02-14   |100|
|14 | 12   |   2021-02-15   |100|
|15 | 12   |   2021-02-16   |100|
|16 | 15   |   2021-02-04   |100|
|17 | 15   |   2021-02-06   |100|
|18 | 15   |   2021-02-10   |100|

我期待什么：

| ID| customer| sale_date | amount|
=====================================
|1   |10   |   2021-02-13 |100|
|2   |12   |   2021-02-13 |100|

标签： mysql

Answer 1

在 MySQL 8+ 上，我们可以ROW_NUMBER在这里使用：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY sale_date) rn
    FROM yourTable
)

SELECT ID, customer, sale_date, amount
FROM cte
WHERE rn = 5 AND sale_date >= '2021-02-01' AND sale_date < '2021-02-16;