1
votes

MYSQL - Grouper par et sélectionner le résultat le plus élevé et utiliser JOIN?

J'ai 2 tables SQL, je dois extraire l'horodatage le plus élevé de la 2ème table et cet horodatage doit être compris dans une plage.

1,Project1,100,1547038287
5,Project5,400,1481118287

J'ai joué avec PHP pour exécutez d'abord une requête qui a une jointure interne afin que je puisse extraire les données des deux tables.

SELECT
project.ID,
project.name,
project.invoiceTotal,
manning.timestamp
FROM
manning
INNER JOIN projects ON project.ID = manning.ProjectID
WHERE
project.department = 1 AND
manning.timestamp BETWEEN 1547038287 AND 1301118287
ORDER BY
manning.timestamp ASC

Je fais ensuite une boucle avec PHP et j'exécute du code procédural pour trier les données, puis l'afficher. Le problème est que c'est lent, et ce n'est certainement pas le moyen le plus efficace de le faire.

Donc, avec cet exemple de données, j'aimerais avoir le résultat:

Table: Projects 
ID,Name,invoiceTotal,department
1,Project1,100,1
2,Project2,200,3
3,Project3,300,2
4,Project4,100,2
5,Project5,400,1


Table: Manning
ProjectID,timestamp
1,1547038287
1,1515558287
1,1501118287
5,1471118287
5,1481118287

J'ai essayé d'utiliser le "groupe par" mais si le L'ordre de la table des effectifs n'est pas correct. Je n'obtiens pas les bons résultats.

Merci pour toute aide. J'ai essayé d'utiliser sqlfiddle.com, mais il semble que cela ne fonctionne pas pour moi en ce moment :(

sql mysql

3 commentaires

Double possible de Obtenir des enregistrements avec une valeur maximale pour chaque groupe de résultats SQL groupés

manning.timestamp BETWEEN 1547038287 AND 1301118287 est l'abréviation de manning.timestamp> = 1547038287 AND manning.timestamp <= 1301118287 , ce qui n'est jamais vrai, car 1547038287 est supérieur à 1301118287.

@ThorstenKettner a noté, un oubli de ma part lors de la dénonciation de l'exemple de requête.

3 Réponses :

0
votes

Ne joignez pas toutes les lignes de gestion, mais seulement la dernière par projet:

select
  p.ID,
  p.name,
  p.invoiceTotal,
  m.max_timestamp
from projects p
join
(
  select projectid, max(timestamp) as max_timestamp
  from manning
  where timestamp between 1301118287 and 1547038287
  group by projectid
) m on m.projectid = p.id
where p.department = 1;

Ce n'est pas seulement une manière propre d'écrire cette requête, mais aussi une approche très performante. Pourquoi rejoindre tous les records, alors que vous n'êtes intéressé que par très peu d'entre eux? Ne produisez pas un résultat intermédiaire énorme dont vous devez vous débarrasser à nouveau.

1 commentaires

Merci à tous ceux qui ont répondu et mis leur temps à cela. Cela fonctionne et beaucoup plus vite qu'avant :)

0
votes

Essayez:

SELECT
    p.ID,
    p.name,
    p.invoiceTotal,
    maxm.ts
FROM
    project p
    INNER JOIN (
        SELECT m.projectID, max(m.timestamp) ts
        FROM manning m
        WHERE p.ID = m.ProjectID AND m.timestamp BETWEEN 1301118287 AND 1547038287
        GROUP BY m.projectID
    ) maxm ON maxm.projectID = p.id
ORDER BY m.timestamp ASC

0 commentaires

0
votes

Si vous utilisez MySQL 8+ ou une version ultérieure, une bonne façon de gérer cela est d'utiliser ROW_NUMBER():

WITH cte AS (
    SELECT p.ID, p.Name, p.invoiceTotal, m.timestamp,
        ROW_NUMBER() OVER (PARTITION BY m.ProjectID ORDER BY m.timestamp DESC) rn
    FROM Projects p
    INNER JOIN Manning m
        ON p.ID = m.ProjectID
    WHERE m.timestamp BETWEEN 1301118287 and 1547038287
)

SELECT ID, Name, invoiceTotal, timestamp
FROM cte
WHERE rn = 1;

Démo h2>

Si vous utilisez une version de MySQL antérieure à 8 et que vous souhaitez pouvoir réduire la quantité de code que vous devez écrire pour de nombreux types de requêtes, envisagez de procéder à une mise à niveau. Consultez les autres réponses pour savoir comment aborder votre problème sur les versions antérieures de MySQL.

0 commentaires