sql - 嵌套连接中的单行

Question

我有以下查询：

SELECT
    COALESCE(purchase.entry.code,'') || coalesce(purchase.linentry.linecod,'') AS pkey,
    purchase.entry.code,
    purchase.linetry.linecod,
    purchase.entry.date,
    purchase.invoice.ninvoice,
    purchase.invoice.dateinvoice,
    gen.provider.code AS code1,
    gen.provider.name,
    gen.provider.name2,
    gen.provider.vatno,
    purchase.vlstinvoice.total,
    purchase.vlstinvoice.vat,
    purchase.vlstinvoice.totalVat
FROM
    purchase.entry  
JOIN
    purchase.linentry ON purchase.linentry.codentry = purchase.entry.code  
JOIN
    purchase.lininvoice ON purchase.lininvoice.codlinentry = purchase.linentry.code  
JOIN
    purchase.invoice ON purchase.invoice.code = purchase.lininvoice.codinvoice  
JOIN
    gen.provider ON gen.provider.code = purchase.entry.codprovider  
JOIN 
    purchase.vlstinvoice ON purchase.vlstinvoice.ninvoice = purchase.invoice.ninvoice

查询结果给了我重复的 entry.code 项。我需要每行中具有第一个或最后一个 linentry.linecod 的唯一 entry.code（因为每个 entry.code 都可以有多个 linentry.linecode）。

标签： sqlpostgresqljoin

Answer 1

使用distinct on. 以下是第一个linecod：

select distinct on (purchase.entry.code)
       . . .  -- rest of columns
from . . .
order by purchase.entry.code, purchase.linentry.linecod;