我有桌子Matieres和Resultats
___ Matieres ___ ___ Resultats ___
id (int auto increment) ID (int auto_increment)
Nom (varchar) TIME_OF_INSERTION (datetime current_timestamp)
Categorie (varchar) ID_USER (int)
Active (boolean) ID_MATIERE (int)
RESULTAT (int)
我有这个要求：
SELECT * FROM Matieres LEFT JOIN Resultats ON Matieres.id = Resultats.ID_MATIERE AND Resultats.ID_USER = :userid WHERE Active = TRUE AND ID_Formation = :formation)
问题在于，对于每个Matieres，它都会向我返回用户在表Resultats中插入的每个结果。当我只想为每个插入最后一个Matieres.Nom...：/
我试过了，GROUP BY TIME_OF_INSERTION 但是不起作用:(
您可以使用row_number()：
SELECT *
FROM Matieres m LEFT JOIN
(SELECT r.*,
ROW_NUMBER() OVER (PARTITION BY id_Matiere, id_user ORDER BY time_of_insertions DESC) as seqnum
FROM Resultats r
WHERE r.ID_USER = :userid
) r
ON m.id = r.ID_MATIERE AND
seqnum = 1
WHERE Active = TRUE AND ID_Formation = :formation;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句