Utilisation de GROUP BY et ORDER BY sur une JOINTURE INTERNE de la requête SQL
J'utilise la requête suivante pour le groupe de travail, les heures et les frais pour les clients de trois tables, une pour les clients, l'une pour les temps de travail et l'autre pour les dépenses:
SELECT a.*,
COALESCE(b.totalCount, 0) AS CountWork,
COALESCE(b.totalAmount, 0) AS WorkTotal,
COALESCE(c.totalCount, 0) AS CountExpense,
COALESCE(c.totalAmount, 0) AS ExpenseTotal
FROM clients A
LEFT JOIN
(
SELECT Client,
COUNT(*) totalCount,
SUM(Amount) totalAmount
FROM work_times
WHERE DATE BETWEEN '2013-01-01' AND '2013-02-01'
GROUP BY Client
) b ON a.Client = b.Client
LEFT JOIN
(
SELECT Client,
COUNT(*) totalCount,
SUM(Amount) totalAmount
FROM expenses
WHERE DATE BETWEEN '2013-01-01' AND '2013-02-01'
GROUP BY Client
) c ON a.Client = c.Client
WHERE b.Client IS NOT NULL OR
c.Client IS NOT NULL
Vous pouvez voir la requête de travail dans un violon ici.
Je suis en train de modifier la présente requête, de sorte qu'il y a une ligne pour chaque client, pour chaque mois, triés par mois et par client. Je suis en train de le faire avec la suite modifié la requête:
SELECT a.*,
COALESCE(b.totalCount, 0) AS CountWork,
COALESCE(b.totalAmount, 0) AS WorkTotal,
COALESCE(c.totalCount, 0) AS CountExpense,
COALESCE(c.totalAmount, 0) AS ExpenseTotal
FROM clients A
LEFT JOIN
(
SELECT Client,
COUNT(*) totalCount,
SUM(Amount) totalAmount,
SUBSTR(Date, 1, 7) as Month
FROM work_times
GROUP BY Month,Client
ORDER BY Month
) b ON a.Client = b.Client
LEFT JOIN
(
SELECT Client,
COUNT(*) totalCount,
SUM(Amount) totalAmount,
SUBSTR(Date, 1, 7) as Month
FROM expenses
GROUP BY Month,Client
ORDER BY Month,Client
) c ON a.Client = c.Client
WHERE b.Client IS NOT NULL OR
c.Client IS NOT NULL
Vous pouvez voir la version modifiée de la requête en action ici.
Il ne fonctionne pas tout à fait juste. Une seule ligne est retournée pour le Client B, même si il y a un temps de travail en janvier 2013 et une charge en février 2013 (donc il devrait y avoir 2 lignes) et il semble que les lignes sont en cours de commande par le Client plutôt que le Mois. Quelqu'un pourrait-il suggérer comment modifier la requête pour obtenir le résultat souhaité qui pour l'exemple sur la deuxième violon serait:
╔════════╦═══════════╦═══════════╦══════════════╦══════════════╗
║ CLIENT ║ COUNTWORK ║ WORKTOTAL ║ COUNTEXPENSE ║ EXPENSETOTAL ║
╠════════╬═══════════╬═══════════╬══════════════╬══════════════╣
║ A ║ 1 ║ 10 ║ 1 ║ 10 ║
║ B ║ 1 ║ 20 ║ 0 ║ 0 ║
║ A ║ 1 ║ 15 ║ 0 ║ 0 ║
║ B ║ 0 ║ 0 ║ 1 ║ 10 ║
║ C ║ 1 ║ 10 ║ 0 ║ 0 ║
╚════════╩═══════════╩═══════════╩══════════════╩══════════════╝
ORDER BY
pour les deux sous-sélectionne. Juste mis pour la réponse finale.Si la date est un type de données datetime, pourquoi/comment faire un substring() sur celui-ci?
Vos tables de données redondantes, le montant et la date. Vous avez besoin de plus de normaliser votre structure et de se débarrasser de la redondance. La requête est trop ambigu, vous êtes désireux de voir les données spécifiques, mais il n'y a pas de critères en tout sélectionner d'autres que id de client n'est pas null. Si vous souhaitez afficher les données d'une plage de dates spécifiée, regroupés par mois, une requête pour la date. J'avais du recul et regarder la normalisation mon premier problème.
Je ne suis pas sûr de ce que tu veux dire. Je suis à l'aide de montant pour calculer la SOMME et la date de groupe par mois.
OriginalL'auteur Nick | 2013-02-24
Vous devez vous connecter pour publier un commentaire.
À moins que quelque chose m'échappe dans les exigences, ce que vous devez faire est d'obtenir une liste de clients et les dates, puis joignez-le à votre sous-requêtes. Donc, votre requête sera:
Voir SQL jouer avec la Démo.
Le résultat est:
heureux de vous aider, j'ai juste fait une petite modif pour la requête d'origine, mais il retourne toujours le même résultat. 🙂
Le résultat de votre travail d'édition ne semble pas être tout à fait le même. Il n'est pas triée par Mois
Tous vous avez besoin est d'ajouter une
ORDER BY
de la dernière requête pour obtenir le bon de commande -- voir mon edit et cette démo --sqlfiddle.com/#!2/fd61b/62Ah, Merci encore.
OriginalL'auteur Taryn
Si vous faites un ordre par une sous-requête, il n'a pas d'importance, parce que la requête externe peut (et peut-être besoin de ré-ordonner les résultats. Vous souhaitez ajouter un order by de la requête externe.
Votre problème est que vous essayez de commander par mois et le client de la table, et également les commander par le mois et le client de la table. Vous devez définir l'ordre de B. mois, B. client, et C. le mois et le mettre dans un ordre par pour la requête externe.
BTW, si vous n'groupe par mois dans la sous-requête pour la table C, alors le client n'est pas significatif. Certaines bases de données, comme DB2, ne vous permettent pas de mettre un non agrégées dans un champ, sélectionnez si elle n'est pas dans le groupe.
Month,Client
dans le violon. J'ai modifié la requête dans ma question pour en tenir compte. Pas sûr que je peux obtenir le résultat que je suis à la recherche de par le simple ajout d'un ORDER BY de la requête externe. J'ai essayé quelques combinaisons et il ne fonctionne pas. Une partie du problème est que les frais et le temps de travail pour le Client B se sont combinées dans la même ligne, même si elles sont dans un autre mois.OriginalL'auteur Marlin Pierce