Requête SQL pour joindre deux tables en fonction de l'horodatage le plus proche

J'ai deux tables en SQL et j'ai besoin d'être en mesure de faire une jointure basée sur l'horodatage dans la table B, qui est antérieure ou égale à l'horodatage dans le tableau A.

Donc, voici quelques fausses données pour les deux tables et la sortie désirée:

Fermé Des Cas (Tableau A)

| id | résolution | timestamp | 
------------------------------------------------ 
| 1 | résolu | 2006-10-05 11:55:44.888153 | 
| 2 | fermé | 2007-10-07 12:34:17.033498 | 
| 3 | saccagé | 2008-10-09 08:19:36.983747 | 
| 4 | résolu | 2010-10-13 04:28:14.348753 |

Classification (Tableau B)

 
| id | valeur | timestamp | 
------------------------------------------------- 
| 1 | recrue | 2006-01-01 12:02:44.888153 | 
| 2 | deuxième | 2007-01-01 12:01:19.984333 | 
| 3 | junior | 2008-01-01 12:02:28.746149 |

Résultats Souhaités

| id | résolution | timestamp | valeur | 
-------------------------------------------------------------- 
| 1 | résolu | 2006-10-05 11:55:44.888153 | recrue | 
| 2 | fermé | 2007-10-07 12:34:17.033498 | deuxième | 
| 3 | saccagé | 2008-10-09 08:19:36.983747 | junior | 
| 4 | résolu | 2010-10-13 04:28:14.348753 | junior |

Donc, je sais que le code doit ressembler à la suivante, je ne peux pas comprendre de quoi faire avec le ON partie de la JOIN (1$et 2 $sont des variables qui seront passés):

SELECT case.id, case.resolution, case.timestamp, class.value
  FROM closed_cases AS case
  LEFT JOIN classifications AS class ON ???
  WHERE case.timestamp BETWEEN $1 AND $2;

Je sais que je pourrais utiliser une sous-sélection, mais ce sera d'exploitation sur au moins quelques milliers de lignes, probablement plus, et j'en ai besoin pour être très rapide, donc je m'attendais à une simple clause qui pourrait le faire.

source d'informationauteur Topher Fangio

  1. 8

    Si vous pouvez apporter des modifications aux structures de la table, je recommande de changer le tableau de classification pour inclure une date de fin, ainsi que la date de début - il sera beaucoup plus facile à se joindre à la table de cette façon.

    Si pas, je suggère ce qui suit:

    SELECT case.id, case.resolution, case.timestamp, class.value
  FROM closed_cases AS case
  LEFT JOIN (select c.*, 
                    (select min(timestamp)
                     from classifications c1
                      where c1.timestamp > c.timestamp) timeend
             from classifications c) AS class 
  ON case.timestamp >= class.timestamp and 
     (case.timestamp < class.timeend or class.timeend IS NULL)
  WHERE case.timestamp BETWEEN $1 AND $2;

    EDIT - avec la date de fin sur la classification:

    SELECT case.id, case.resolution, case.timestamp, class.value
  FROM closed_cases AS case
  LEFT JOIN classifications AS class 
  ON case.timestamp >= class.timestamp and case.timestamp < class.timeend
  WHERE case.timestamp BETWEEN $1 AND $2;
  2. 0

    modifier l'horodatage et l'utilisation d'un int comme une clé pour connecter les tables. cela fonctionne beaucoup plus vite, puis en comparant la date

    tableau 1
    champ1
    field2
    field3
    ConnectorField

    table2
    champ1
    field2
    field3
    ConnectorField

    et tout ce que vous devez faire est de
    select * from table1 T1 inner join table2 T2 on T1.ConnectorField = T2.ConnectorField

  3. -1
    SELECT case.id, case.resolution, case.timestamp, class.value
  FROM closed_cases AS case
  LEFT JOIN classifications AS class 
  ON case.timestamp >= class.timestamp
  WHERE case.timestamp BETWEEN $1 AND $2;