sql - 最后一张发票使用 Postgres

Question

我有一个包含三个表的 Postgres 9.1 数据库 - Customer、Invoice 和 Line_Items

我想创建一个客户列表，显示具有特定项目(特别是具有 line_items.code 以“L3”开头的所有发票)的客户和上次发票日期。

首先，我试图为每个客户拉取一笔交易(最后一张带有“L3”代码的发票)(假设我可以在创建此列表后加入客户名称)。

表格是这样的:

客户

cust_number   last_name   first_name
===========   ========   ====================
1             Smith      John
2             Jones      Paul
3             Jackson    Mary
4             Brown      Phil

交易

trans_number   date            cust_number
===========   ===========      ====================
1001          2014-01-01        1
1002          2014-02-01        4
1003          2014-03-02        2
1004          2014-03-06        3

行项目

trans_number   date            item_code
===========   ===========      ====================
1001          2014-01-01        L3000
1001          2014-01-01        M2420
1001          2014-01-01        L3500
1002          2014-02-01        M2420
1003          2014-03-02        M2420
1004          2014-03-06        L3000

到目前为止，我有:

Select transactions.cust_number, transactions.trans_number
from transactions 
where transactions.trans_number in 
    ( SELECT  Line_Items.trans_number
        FROM Line_Items
        WHERE Line_Items.item_code ilike 'L3%'
        ORDER BY line_items.date DESC
    )
order by transactions.pt_number

这会为每个客户提取发票上带有“L3”代码的所有发票，但我不知道如何只获得最后一张发票。

Answer 1

使用DISTINCT ON:

SELECT DISTINCT ON (t.cust_number)
       t.cust_number, t.trans_number
FROM   line_items l
JOIN   transactions t USING (trans_number)
WHERE  l.item_code ILIKE 'L3%'
ORDER  BY t.cust_number, l.date DESC;

每个 cust_number 最多返回一行 - latest trans_number。您可以自由地将更多列添加到 SELECT 列表。

详细解释:

• Select first row in each GROUP BY group?