mysql - 如何获取postgresql中特定行的最近日期

Question

我有以下问题:

我有一个包含两种类型数据的表。一个状态 = 1，一个状态 = 2。对于状态 = 2 的每一行，我需要状态 = 1 的最后对应日期。这是一个示例:

date           state    id
'2016-04-03'   1        1
'2016-04-04'   1        1
'2016-04-05'   2        1
'2016-04-06'   2        1

预期结果:

date           lastdate       state    id
'2016-04-03'   '2016-04-03'   1        1
'2016-04-04'   '2016-04-04'   1        1
'2016-04-05'   '2016-04-04'   2        1
'2016-04-06'   '2016-04-04'   2        1
'2016-04-07'   '2016-04-07'   1        1

我不完全知道我被困在哪里。感觉您以错误的方式看待这个问题，因此不胜感激。

提前致谢!

编辑

似乎我没有很好地解释我的问题，这是另一种尝试:

对于每一行，我需要最后一行具有相同 ID 且状态 = 1 的日期。如果状态已经为 1，则需要同一行的日期。

所以为了澄清我的“预期结果”- 表:

date           lastdate       state    id
'2016-04-03'   '2016-04-03'   1        1   

状态为 1，所以行的日期是状态 1 的最后日期

'2016-04-04'   '2016-04-04'   1        1

状态为 1，所以行的日期是状态 1 的最后日期

'2016-04-05'   '2016-04-04'   2        1

状态为 2，所以最后一行日期为 1 的日期来自前一行

'2016-04-06'   '2016-04-04'   2        1

state 为 2，state = 1 的最后日期为 '2016-04-04'

'2016-04-07'   '2016-04-07'   1        1

state 又是 1，所以是同一个日期

'2016-04-08'    '2016-04-07'  2        1

状态为 2，因此设置了最后一行(前一行)的日期

我希望我的意图现在是可以理解的。

Answer 1

这可以通过将表与自身连接来实现:

select distinct on (a.date) 
    a.date date, 
    coalesce(b.date, a.date) last_date, 
    a.state, 
    a.id
from test a
left join test b 
on a.state = 2 and b.state = 1 and b.date < a.date
order by a.date, b.date desc;

    date    | last_date  | state | id 
------------+------------+-------+----
 2016-04-03 | 2016-04-03 |     1 |  1
 2016-04-04 | 2016-04-04 |     1 |  1
 2016-04-05 | 2016-04-04 |     2 |  1
 2016-04-06 | 2016-04-04 |     2 |  1
 2016-04-07 | 2016-04-07 |     1 |  1
(5 rows)