SQL:查找具有重复值的最小、最大日期

Question

如果我有如下数据，并且我需要按日期查找每个促销订单的最小和最大日期请帮我编写 SQL 脚本

数据:
Sample Data

Data:   
month           promotion
31/01/2019          A
28/02/2019          A
31/03/2019          B
30/04/2019          C
31/05/2019          C
30/06/2019          C
31/07/2019          C
31/08/2019          C
30/09/2019          B
31/10/2019          B
30/11/2019          B
31/12/2019          B

需要结果:
Need result

need result:        
min_month   max_month   promotion
31/01/2019  28/02/2019  A
31/03/2019  31/03/2019  B
30/04/2019  31/08/2019  C
30/09/2019  31/12/2019  B

我得到的结果:不正确
result i got: incorrect

min_month   max_month   promotion
31/01/2019  28/02/2019  A
31/03/2019  31/12/2019  B
30/04/2019  31/08/2019  C

谢谢

我的脚本

select promotion, min(month) min_month, max(month) max_month  
from table  
group by promotion

Answer 1

这是间隙和岛屿问题的形式。如果您的 DBMS 支持窗口函数，您可以尝试以下查询 -

select min(month) min_month, max(month) max_month, promotion
from (select promotion,
             month,
             rank() over(order by month) seqnum1,
             rank() over(partition by promotion order by month) seqnum2
      from table) t
group by promotion, seqnum1 - seqnum2;