Postgres: left join avec order by et limit 1

J'ai la situation:

Table1 has a list of companies.
Table2 has a list of addresses.
Table3 is a N relationship of Table1 and Table2, with fields 'begin' and 'end'.

Parce que les entreprises peuvent se déplacer au fil du temps, une JOINTURE GAUCHE parmi eux, plusieurs enregistrements pour chaque entreprise.

begin et end les champs ne sont jamais NULLES. La solution pour trouver l'adresse la plus récente est l'utilisation d'un ORDER BY being DESC, et à supprimer les anciens adresses est un LIMIT 1.

Qui fonctionne bien si la requête peut porter seulement 1 compagnie. Mais j'ai besoin d'une requête qui rassemble tous Table1 dossiers, rejoint avec leur Table2 adresses. Par conséquent, la suppression des données obsolètes doit être fait (à ma connaissance) à GAUCHE de la JOINTURE SUR la clause.

Une idée de comment je peux construire la clause pour ne pas créer dupliqué Table1 entreprises et apporter de la dernière adresse?

OriginalL'auteur Hikari | 2014-02-17

  1. 15

    Utiliser un dépendante de la sous-requête avec max (), fonction dans une condition de jointure.

    Quelque chose comme dans cet exemple:

    SELECT *
FROM companies c
LEFT JOIN relationship r
ON c.company_id = r.company_id
   AND r."begin" = (
        SELECT max("begin")
        FROM relationship r1
        WHERE c.company_id = r1.company_id
     )
INNER JOIN addresses a
ON a.address_id = r.address_id

    démo: http://sqlfiddle.com/#!15/f80c6/2

    Nice sur sqlfiddle - je ne l'ai vu jsfiddle jusqu'à maintenant, mais sqlfiddle semble superhelpful!

    OriginalL'auteur krokodilko

  2. 5

    J'ai réussi à le résoudre à l'aide de Windows Fonction:

    WITH ranked_relationship AS(
    SELECT
        *
        ,row_number() OVER (PARTITION BY fk_company ORDER BY dt_start DESC) as dt_last_addr
    FROM relationship
)

SELECT
    company.*
    address.*,
    dt_last_addr as dt_relationship
FROM
    company
    LEFT JOIN ranked_relationship as relationship
            ON relationship.fk_company = company.pk_company AND dt_last_addr = 1
    LEFT JOIN address ON address.pk_address = relationship.fk_address

    row_number() crée un int compteur pour chaque enregistrement, à l'intérieur de chaque fenêtre en fonction de fk_company. Pour chaque fenêtre, le dossier avec la date la plus récente vient en premier avec rang 1, puis dt_last_addr = 1 permet de s'assurer de la JOINTURE n'arrive qu'une fois pour chaque fk_company, avec l'enregistrement avec la dernière adresse.

    Les Fonctions de la fenêtre sont très puissants et quelques ppl de les utiliser, ils évitent beaucoup de complexes, jointures et sous-requêtes!

    OriginalL'auteur Hikari