SQL Obtenir un agrégat de 0 pour une ligne non existante en utilisant des jointures internes.

Question

J'utilise SQL Server pour interroger ces trois tables qui ressemblent à (il y a quelques colonnes supplémentaires mais pas très pertinentes) :

• Clients -> Id, Nom
• Adresses -> Id, Rue, RueNo, ClientId
• Ventes -> ID d'adresse, Semaine, Total

Et je voudrais obtenir le total des ventes par semaine et par client (en affichant en même temps les détails de l'adresse). J'ai trouvé la requête suivante

SELECT a.Name, b.Street, b.StreetNo, c.Week, SUM (c.Total) as Total
FROM Customers a
    INNER JOIN Addresses b ON a.Id = b.CustomerId
    INNER JOIN Sales c ON b.Id = c.AddressId
GROUP BY a.Name, c.Week, b.Street, b.StreetNo

et même si mes compétences en SQL sont proches de zéro, il semble qu'il fasse son travail. Mais maintenant, j'aimerais pouvoir afficher 0 chaque fois qu'un client n'a pas de ventes pour une semaine particulière (les semaines sont juste des nombres entiers). Et je me demande si je ne devrais pas obtenir des valeurs distinctes des semaines dans la table Sales, et ensuite les parcourir en boucle (je ne sais pas comment).

Une aide ?

Gracias

Answer 1

Utilisez CROSS JOIN pour générer les lignes pour tous les clients et toutes les semaines. Utilisez ensuite LEFT JOIN pour apporter les données qui sont disponibles :

SELECT c.Name, a.Street, a.StreetNo, w.Week,
       COALESCE(SUM(s.Total), 0) as Total
FROM Customers c CROSS JOIN
     (SELECT DISTINCT s.Week FROM sales s) w LEFT JOIN
     Addresses a
     ON c.CustomerId = a.CustomerId LEFT JOIN
     Sales s
     ON s.week = w.week AND s.AddressId = a.AddressId
GROUP BY c.Name, a.Street, a.StreetNo, w.Week;

L'utilisation d'alias de table est une bonne chose, mais les alias doivent être des abréviations des noms de table. Ainsi, a pour Addresses no Customers .

Answer 2

Veuillez essayer ce qui suit...

SELECT Name,
       Street,
       StreetNo,
       Week,
       SUM( CASE
                WHEN Total IS NULL THEN
                    0
                ELSE
                    Total
            END ) AS Total
FROM Customers a
JOIN Addresses b ON a.Id = b.CustomerId
RIGHT JOIN Sales c ON b.Id = c.AddressId
GROUP BY a.Name,
         c.Week,
         b.Street,
         b.StreetNo;

J'ai modifié votre déclaration à trois endroits. Le premier est que j'ai changé votre jointure en Sales à un RIGHT JOIN . Ceci se joindra comme il le ferait avec un INNER JOIN mais il conservera également les enregistrements de la table sur le site Web de l'entreprise. droite côté de la JOIN qui n'ont pas d'enregistrement ou de groupe d'enregistrements correspondants sur la gauche, en plaçant NULL les valeurs dans les champs de l'ensemble de données résultant qui proviendraient de la gauche de l'ensemble de données. JOIN . A LEFT JOIN fonctionne de la même manière, mais avec tous les enregistrements supplémentaires de la table sur le serveur de l'entreprise. gauche en cours de conservation.

J'ai supprimé le mot INNER de votre survivant INNER JOIN . Où JOIN n'est pas précédé d'un type de jointure, un INNER JOIN est effectuée. Les deux sites JOIN y INNER JOIN sont considérées comme correctes, mais le protocole qui prévaut semble être de laisser les INNER ou lorsque le SGBDR permet de le laisser de côté (ce que fait SQL-Server). C'est à vous de choisir. Je l'ai laissé ici à titre d'exemple.

Le troisième changement est que j'ai ajouté un CASE qui vérifie si le Total contient un NULL ce qui sera le cas s'il n'y a pas eu de ventes pour ce client pendant cette semaine. Si c'est le cas, alors SUM() retournerait un NULL donc le CASE renvoie un 0 à la place. Si Total ne contient pas de NULL la valeur SUM() de toutes les valeurs de Total pour ce regroupement est effectuée.

Veuillez noter que je suppose que Total n'aura pas de NULL des valeurs autres que celles de l RIGHT JOIN . Veuillez me dire si cette hypothèse est incorrecte.

Veuillez également noter que j'ai supposé que soit il n'y aura pas de manquant. Week s pour un Customer dans le Sales ou que vous n'êtes pas intéressé à les répertorier s'ils existent. Encore une fois, veuillez m'informer si cette hypothèse est incorrecte.

Si vous avez des questions ou des commentaires, n'hésitez pas à poster un commentaire en conséquence.

Answer 3

Vous devriez générer des numéros de semaine, plutôt que d'utiliser DISTINCT . Cette solution est meilleure en termes de performances et de fiabilité. Utilisez ensuite un LEFT JOIN sur la table des ventes au lieu d'un INNER JOIN :

SELECT   a.Name
        ,b.Street
        ,b.StreetNo
        ,weeks.[Week]
        ,COALESCE(SUM(c.Total),0) as Total
FROM Customers a
    INNER JOIN Addresses b ON a.Id = b.CustomerId
    CROSS JOIN (
        -- Generate a sequence of 52 integers (13 x 4)
        SELECT ROW_NUMBER() OVER (ORDER BY a.x) AS [Week]
        FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(x)
        CROSS JOIN (SELECT x FROM (VALUES(1),(1),(1),(1)) b(x)) b
    ) weeks
    LEFT JOIN Sales c ON b.Id = c.AddressId AND c.[Week] = weeek.[Week]
GROUP BY a.Name
        ,b.Street
        ,b.StreetNo
        ,weeks.[Week]