如何在postgres sql中获得第二个最大日期

I have following situation where i need to get several values between two invoices date.

我有以下情况，我需要在两个发票日期之间获得几个值。

So query is giving data based on invoices now what i need to do is for some values fetch data between this invoice date and last invoice date

所以查询现在根据发票提供数据我需要做的是获取一些值来获取此发票日期和最后发票日期之间的数据

already tried ways 1) sub query will easily solve this but as i have to do this for 4-5 column and its a 15 gb database so that's not possible. 2) if i go like this

已经尝试过的方法1）子查询将很容易解决这个问题，但因为我必须为4-5列及其15 gb数据库执行此操作，因此这是不可能的。 2）如果我这样

left join (select inv.date ,inv,actno from invoice inv) as invo on invo.actno=act.id and invo.date < inv.date

then it will give all the data less then that date but i need only one data that will be less than main invoice date.

然后它会提供比该日期少的所有数据，但我只需要一个小于主发票日期的数据。

3) we can not get second max value in subquery of from clause because outer invoice is not grouped so it might be max or midlle or least .

3）我们不能在from子句的子查询中得到第二个最大值，因为外部发票没有分组，所以它可能是max或midlle或者最小。

4) we can not send values of other table in subquery of join table.

4）我们不能在连接表的子查询中发送其他表的值。

ex

前

create table inv (id serial ,date timestamp without time zone);

insert into inv (date) values('2017-01-31 00:00:00'),('2017-01-30 00:00:00'),('2017-01-29 00:00:00'),('2017-01-28 00:00:00'),('2017-01-27 00:00:00');

select date as d1 from inv;
 id |        date         
----+---------------------
  1 | 2017-01-31 00:00:00
  2 | 2017-01-30 00:00:00
  3 | 2017-01-29 00:00:00
  4 | 2017-01-28 00:00:00
  5 | 2017-01-27 00:00:00

(5 rows)

I need this

我需要这个

 id |date                 |date                 | id 
  1 | 2017-01-31 00:00:00 | 2017-01-30 00:00:00 |  2
  2 | 2017-01-30 00:00:00 | 2017-01-29 00:00:00 |  3
  3 | 2017-01-29 00:00:00 | 2017-01-28 00:00:00 |  4
  4 | 2017-01-28 00:00:00 | 2017-01-27 00:00:00 |  5
  5 | 2017-01-27 00:00:00 |  

I can't do subquery in select as database is big and need to do this for 4-5 column

我不能在select中做subquery，因为数据库很大，需要为4-5列做这个

UPDATE 1

更新1

I need this from same table but using it twice in FROM clause as my requirement is that I need several data joined from invoice table and then there is 4-5 column in which I need things like sum of amount paid between last and this invoice.

我需要从同一个表中使用它，但在FROM子句中使用它两次，因为我的要求是我需要从发票表中加入几个数据，然后有4-5列，其中我需要在最后一个和此发票之间支付的金额之和。

So I can take both invoice date in subquery and get the data between them

所以我可以在子查询中获取发票日期并获取它们之间的数据

UPDATE 2

更新2

lag will not solve this

滞后不会解决这个问题

select i.id,i.date, lag(date) over (order by date) from inv i order by id ;
id |        date         |         lag        
----+---------------------+---------------------
1 | 2017-01-31 00:00:00 | 2017-01-30 00:00:00
2 | 2017-01-30 00:00:00 | 2017-01-29 00:00:00
3 | 2017-01-29 00:00:00 | 2017-01-28 00:00:00
4 | 2017-01-28 00:00:00 | 2017-01-27 00:00:00
5 | 2017-01-27 00:00:00 | 
(5 rows)
Time: 0.480 ms
test=# select i.id,i.date, lag(date) over (order by date) from inv i where id=2 order by id ;
 id |        date         | lag 
----+---------------------+-----
  2 | 2017-01-30 00:00:00 | 
(1 row)
Time: 0.525 ms
test=# select i.id,i.date, lag(date) over (order by date) from inv i where id in (2,3) order by id ;
 id |        date         |         lag       
----+---------------------+---------------------
2 | 2017-01-30 00:00:00 | 2017-01-29 00:00:00
3 | 2017-01-29 00:00:00 | 

it will calculate on the data it will get from the table in that query it is bounded in that query see here 3 has a lag but could not get it cause query is not allowing it to have it ....something in left join needs to be done so the lag date can be taken from same table but calling it again in from clause Thanks Again buddy

它将计算它将从该查询中的表中得到的数据它在该查询中有界限看到这里3有一个滞后但无法得到它因为查询不允许它拥有它....左边连接需要的东西要做到这样，滞后日期可以从同一个表中获取，但再次从子句中再次调用它再次感谢好友

1 个解决方案

#1

Like here?:

像这儿？：

t=# select date as d1,
           lag(date) over (order by date) 
    from inv 
    order by 1 desc;


         d1          |         lag
---------------------+---------------------
 2017-01-31 00:00:00 | 2017-01-30 00:00:00
 2017-01-30 00:00:00 | 2017-01-29 00:00:00
 2017-01-29 00:00:00 | 2017-01-28 00:00:00
 2017-01-28 00:00:00 | 2017-01-27 00:00:00
 2017-01-27 00:00:00 |
(5 rows)

Time: 1.416 ms