J'ai une base de données dans laquelle je stocke les permis de pêche. Les licences ont des dates de début et de fin, et j'utilise un tableau de calendrier pour compter le nombre de licences vendues pour chaque jour. Chaque licence a un type de licence défini. Le type de licence peut être valable pour une ou plusieurs zones de pêche.
J'essaie de créer une requête qui affiche certaines colonnes de la table des zones et le nombre de licences vendues regroupées par jour et par zone de pêche, et filtrées par type de licence. Je l'ai fait fonctionner pour les jours où les licences ont été vendues. Cependant, les jours où aucune licence n'a encore été vendue n'affichent que des valeurs nulles.
Cela fait maintenant 8 heures que je me tord la tête. Je suis sûr qu'il existe une solution simple, je ne peux tout simplement pas la voir. SQL Fiddle ici .
Je pense que le schéma est tout à fait autonome -explicatif, donc je ne l'inclurai pas ici. Veuillez voir le violon si nécessaire.
SET @licensetype = 1,
@fromdate = '2019-01-01',
@todate = '2019-01-10';
SELECT zoneID,
dy,
seasonmax,
daymax,
COUNT(lID) AS sold
FROM
( SELECT z.zoneID,
z.seasonmax,
z.daymax,
l.ID AS lID,
l.From,
l.To,
lt.ValidForZone
FROM zones z
LEFT JOIN licensetypes lt ON z.zoneID IN(lt.ValidForZone)
LEFT JOIN licenses l ON lt.ID = l.TypeID
WHERE FIND_IN_SET( z.zoneID,
( SELECT lt2.ValidForZone
FROM licensetypes lt2
WHERE lt2.ID = @licensetype ) ) ) derived
RIGHT JOIN calendar ON calendar.dy >= DATE_FORMAT(derived.From, '%Y-%m-%d')
AND calendar.dy < DATE_FORMAT(derived.To, '%Y-%m-%d')
WHERE calendar.dy >= @fromdate
AND calendar.dy <= @todate
GROUP BY dy,
zoneID
ORDER BY dy
3 Réponses :
Si vous voulez une ligne pour chaque zone, même celles qui n'ont pas été vendues ce jour-là, vous pouvez retirer le tableau zones de la sous-requête. Vous voulez commencer par un CROSS JOIN entre les tables zones et calendrier pour obtenir toutes les combinaisons de zones et de dates. Puis LEFT JOIN avec les informations de licence pour obtenir le nombre de licences.
De plus, pour joindre une liste séparée par des virgules, vous devez utiliser FIND_IN_SET , pas IN () . Voir Rechercher avec une valeur mysql séparée par des virgules . P >
SELECT z.zoneID, c.dy, z.seasonmax, z.daymax, IFNULL(COUNT(l.ID), 0) AS sold
FROM zones AS z
CROSS JOIN calendar AS c
JOIN licensetypes AS lt ON FIND_IN_SET(z.zoneID, lt.ValidForZone)
LEFT JOIN licenses AS l ON lt.ID = l.typeID AND c.dy >= DATE_FORMAT(l.From, '%Y-%m-%d') AND c.dy < DATE_FORMAT(l.To, '%Y-%m-%d')
WHERE c.dy BETWEEN @fromdate AND @todate
AND lt.ID = @licensetype
GROUP BY z.zoneID, c.dy
ORDER BY dy
Merci, il me reste encore beaucoup à apprendre! :) Cependant, le décompte de votre requête n'est pas correct. Le 3 janvier, il devrait y avoir 1 permis valide pour la zone 70 et 2 valide pour la zone 80. Le 4 janvier, il devrait y en avoir 2 valides pour la zone 70 et 3 pour la zone 80. Le 5 janvier, il devrait y en avoir 1 valide dans la zone 70 et 1 dans la zone 80.
Désolé, je n'ai pas remarqué jusqu'à présent que ma propre requête donne également un mauvais décompte: /
Je pense voir le problème. lt.ID = @licensetype ne doit être utilisé que lors de la sélection des zones à afficher, il ne doit pas être utilisé lors de l'obtention du nombre de licences. Est-ce correct? Je n'ai pas le temps de mettre à jour la requête maintenant, mais cela nécessite deux fois de se joindre à licensetypes : une fois pour obtenir les zones, une autre pour obtenir toutes les licences.
Oui c'est vrai. Les licences peuvent être récupérées par l.TypeID = @licensetype . Je ne pense pas être capable de comprendre cela, alors j'attends avec impatience votre mise à jour.
Pardonnez-moi, votre requête donne le bon décompte. C'est moi qui avais un cerveau péter et oublié que la requête filtre sur le type de licence (comme il se doit).
Merci pour le violon de la base de données. J'ai largement modifié votre requête, conformément aux directives suivantes:
GROUP BY FROM calendrier CROSS JOIN zones ; les filtres généraux issus des paramètres, qui s'appliquent aux calendriers et zones , et peuvent être placés dans la clause WHERE LEFT JOIN ed doit être déplacée vers la clause ON de la jointure appropriée DATE que DATE_FORMAT pour tronquer une date / heure à ce jour Nouvelle requête (voir le violon de base de données ): p>
SELECT z.zoneID, calendar.dy, z.seasonmax, z.daymax, COUNT(DISTINCT l.ID) Asold
FROM
calendar
CROSS JOIN zones z
LEFT JOIN licensetypes lt ON z.zoneID IN(lt.ValidForZone)
LEFT JOIN licenses l
ON lt.ID = l.TypeID
AND calendar.dy BETWEEN DATE(l.From) AND DATE(l.To)
WHERE
FIND_IN_SET( z.zoneID, ( SELECT lt2.ValidForZone FROM licensetypes lt2 WHERE lt2.ID = @licensetype ) )
AND calendar.dy >= @fromdate
AND calendar.dy <= @todate
GROUP BY z.zoneID, calendar.dy, z.seasonmax, z.daymax
ORDER BY dy
NB: cette requête ne prend pas correctement en compte le cas où plusieurs valeurs séparées par des virgules sont stockées dans licensetypes.ValidForZone : lorsque plusieurs types de licence correspondent , seul le premier est compté (tout comme votre requête d'origine). Il serait préférable de normaliser votre base de données et d'utiliser une table de pont pour stocker la relation 1-N entre les types de licence et les zones, au lieu de remplir N valeurs dans un seul champ ...
Merci, il me reste encore beaucoup à apprendre! :) Cependant, le décompte de votre requête n'est pas correct. Le 3 janvier, il devrait y avoir 1 permis valide pour la zone 70 et 2 valide pour la zone 80. Le 4 janvier, il devrait y en avoir 2 valides pour la zone 70 et 3 pour la zone 80. Le 5 janvier, il devrait y en avoir 1 valide dans la zone 70 et 1 dans la zone 80.
@ aanders77: Je vois votre point (votre requête d'origine avait le même problème, donc je n'ai pas fait attention). J'ai peur qu'il soit plus simple de corriger la conception de votre base de données plutôt que de peaufiner la requête ... J'ai mis à jour ma réponse.
Désolé, je n'ai pas remarqué jusqu'à présent que ma propre requête donne également un mauvais décompte.
Je pense que cette requête devrait vous donner les résultats souhaités (qui semble être 1 pour 3/1, 2 pour 4/1 et 5/1). Vous devez partir d'un CROSS JOIN de jours et de zones, puis de LEFT JOIN vers licensingetypes et licences sur le type de licence et jours pertinents:
SELECT z.zoneID, DATE(c.dy) AS dy, z.seasonmax, z.daymax, COUNT(l.ID) AS sold FROM calendar c CROSS JOIN zones z LEFT JOIN licensetypes t ON t.ID = @licensetype AND FIND_IN_SET(z.zoneID, t.ValidForZone) LEFT JOIN licenses l ON l.TypeID = t.ID AND DATE(c.dy) >= DATE(l.From) AND DATE(c.dy) < DATE(l.To) WHERE DATE(c.dy) BETWEEN @fromdate AND @todate GROUP BY dy, z.zoneID
Merci d'avoir répondu. Cependant, je pense que votre décompte est faux? (Comme je l'ai commenté sur les deux autres réponses, le décompte est également faux dans mon propre violon). Si vous consultez le tableau des licences, il y a 3 licences. Deux d'entre eux sont valables le 3 janvier. Le n ° 1 n'est valable que dans la zone 80, tandis que l'autre est valable dans les deux zones (70 et 80). Ainsi, le décompte du 3 janvier doit être 1 pour la zone 70 et 2 pour la zone 80. De plus, la date "à" ne doit pas être incluse dans le décompte.
@ aanders77 le décompte de la licence valide uniquement dans la zone 80 n'est pas inclus car vous vouliez filtrer par @licensetype = 1. N'est-ce pas ce que vous voulez? Lorsque vous faites référence à la date to , voulez-vous dire pour la table des licences, la variable @todate, ou les deux?
Bien sûr, vous avez raison! Il est 10 minutes après minuit ici maintenant, et j'y travaille depuis environ 3 heures cet après-midi. Il est peut-être temps de faire une pause! À propos de la date du au , je veux dire dans le tableau des licences.
@ aanders77 J'ai mis à jour ma réponse en fonction de vos commentaires sur la date du au . Il vérifie toujours le @licensetype , vous pouvez simplement supprimer cette partie de la clause ON et je pense que vous verrez que cela donne les bons résultats dans ce cas aussi.
Que doit-il afficher à la place des valeurs nulles à ces dates? N'est-ce pas le but d'utiliser
RIGHT JOIN calendar, pour qu'il affiche toutes les dates du calendrier, même celles qui ne correspondent à rien dans la première requête?Voulez-vous afficher
0au lieu deNULL? UtilisezIFNULL (COUNT (lID), 0) AS vendu.Mais il n'y a pas d'ID de zone si rien n'a été vendu, que voulez-vous afficher à la place de
NULL? UtilisezIFNULL ()pour remplacer la valeur nulle par ce que vous voulez à la place.Salut, sans rapport: je ne pense pas que vous devriez insérer (1, '70, 80 ') dans les types de licence, voir fr .wikipedia.org / wiki / First_normal_form
Oui, vous avez raison sur le BON JOINT. J'en ai besoin pour afficher une ligne par zone et par jour, avec le numéro de zone, le seasonmax et le daymax du tableau des zones.
@Barmar, il devrait être possible d'afficher le zoneID, seasonmax et daymax même s'il n'y a pas de licences vendues. Ces valeurs sont extraites de la table des zones, qui est jointe à la table des types de licence. J'utilise l'ID de type de licence dans la clause WHERE, donc cela devrait être possible sans licences vendues.
S'il n'y a rien de vendu à cette date, quel ID de zone doit-il afficher pour cette date? Voulez-vous afficher une ligne pour chaque zone, avec un décompte nul pour celles qui n'ont pas été vendues ce jour-là?
Il doit afficher plusieurs lignes par date, une pour chaque ID de zone défini dans le tableau des types de licence. Le nombre doit être égal à zéro et les autres colonnes doivent avoir les valeurs de la table des zones. (Les vraies tables ont bien sûr beaucoup plus de colonnes que le violon. Je vais l'utiliser comme sous-requête pour d'autres calculs).