J'ai trois tables qui doivent être jointes. Je suis capable de joindre deux d'entre eux et get le résultat désiré, et cette table résultante doit être jointe avec une autre table.
Table1 :
+----------+---------+------+ | Username | Country | Team | +----------+---------+------+ | abc | US | New | | abc | CAN | New | | bcd | US | Old | +----------+---------+------+
Table2 :
+----------+-------------+----------+------------+ | Username | CompanyCode | Document | Entry Date | +----------+-------------+----------+------------+ | abc | 1 | 112 | 24/06/2014 | | abc | 2 | 123 | 24/06/2014 | | bcd | 3 | 456 | 24/06/2014 | | efg | 4 | 984 | 24/06/2014 | +----------+-------------+----------+------------+
J'ai écrit le code suivant ..
SELECT Username, CompanyCode, Document, IIF(MONTH([Entry Date]) = 6 AND YEAR([Entry Date]) = 2014, 'TRUE', 'FALSE') AS [Posted], COALESCE(tNew.Country, 'not there') AS DC, COALESCE(tNew.Team, 'not there') AS Team FROM Table2 OUTER APPLY ( SELECT TOP 1 Country, Team FROM Table1 WHERE Table1.[Username] = Table2.[Username] ) tNew
… ce qui donne ( Table99 ) …
+----------+--------------+----------+------------+--------+-----------+-----------+ | Username | Company Code | Document | Entry Date | Posted | Country | Team | +----------+--------------+----------+------------+--------+-----------+-----------+ | abc | 1 | 112 | 24/06/2014 | TRUE | US | New | | abc | 2 | 123 | 24/06/2014 | TRUE | US | New | | bcd | 3 | 456 | 24/06/2014 | TRUE | US | Old | | efg | 4 | 984 | 24/06/2014 | TRUE | not there | not there | +----------+--------------+----------+------------+--------+-----------+-----------+
Maintenant, j'ai une autre table, Table3 :
+--------------+--------------+ | Company Code | Company Name | +--------------+--------------+ | 1 | MS | | 2 | APL | | 3 | GOO | | 4 | IBM | | 5 | AMZ | +--------------+--------------+
Je veux join Table99 avec Table3 sur le Company Code
avec le nombre de Document
O WH WHERE Posted = TRUE AND Country <> 'not there'
résultant en …
+--------------+--------------+-----------------+ | Company Code | Company Name | Total Documents | +--------------+--------------+-----------------+ | 1 | MS | 1 | | 2 | APL | 1 | | 3 | GOO | 1 | | 4 | IBM | 0 | | 5 | AMZ | 0 | +--------------+--------------+-----------------+
Faites un LEFT JOIN
sur Table3
et la requête d'origine, puis utilisez la SUM
conditionnelle pour countr:
SELECT t3.CompanyCode, t3.CompanyName, SUM(CASE WHEN t.Posted = 'TRUE' AND t.Country <> 'not there' THEN 1 ELSE 0 END) FROM Table3 t3 LEFT JOIN ( SELECT Username, CompanyCode, Document, tnew.Country, IIF(MONTH(EntryDate) = 6 AND YEAR(EntryDate) = 2014, 'TRUE', 'FALSE') AS [Posted], COALESCE(tNew.Country, 'not there') AS DC, COALESCE(tNew.Team, 'not there') AS Team FROM Table2 OUTER APPLY( SELECT TOP 1 Country, Team FROM Table1 WHERE Table1.[Username] = Table2.[Username] ) tNew ) t ON t3.CompanyCode = t.CompanyCode GROUP BY t3.CompanyCode, t3.CompanyName ORDER BY t3.CompanyCode
Essayez-le ici
Vous pouvez traiter les tables dérivées comme si elles étaient de vraies tables:
SELECT Username, Table2.CompanyCode, Document, IIF(MONTH([Entry Date]) = 6 AND YEAR([Entry Date]) = 2014, 'TRUE', 'FALSE') AS [Posted], COALESCE(tNew.Country, 'not there') AS DC, COALESCE(tNew.Team, 'not there') AS Team FROM Table2 OUTER APPLY ( SELECT TOP 1 Country, Team FROM Table1 WHERE Table1.[Username] = Table2.[Username] ) tNew JOIN Table3 ON Table2.CompanyCode = Table3.CompanyCode
Je pense que vous avez un peu compliqué votre requête. L'opérateur APPLY
de SQL est principalement destiné à être utilisé avec des fonctions de table. Plus généralement, il peut être utile lorsqu'il n'y a pas de condition de jointure simple entre deux tables.
Dans votre cas, cependant, il existe une condition de jointure très simple: la colonne Username
dans Table1
et Table2
. Les jointures sont l'essence de SQL et (en général) devraient être votre premier port d'escale chaque fois que vous avez besoin de combiner des informations provenant de plusieurs tables.
Donc, dans ce cas, étant donné le résultat final que vous devez atteindre, vous pouvez le faire simplement en utilisant deux jointures à gauche, sans APPLY
:
with PostedDocs as ( -- Define your requirements on the EntryDate value here select CompanyCode ,Username ,Document from Table2 where MONTH(EntryDate) = 6 and YEAR(EntryDate) = 2014 ) select CO.CompanyCode ,CO.CompanyName ,TotalDocuments =count(distinct case when USR.Country is null then null else DOC.Document end) from Table3 CO left join PostedDocs DOC on CO.CompanyCode=DOC.CompanyCode left join Table1 USR on DOC.Username=USR.Username group by CO.CompanyCode ,CO.CompanyName order by CompanyCode asc
Cela donne exactement le résultat que vous voulez, pour les inputs que vous fournissez dans votre question. C'est un mode de pensée plus axé sur le SQL et susceptible de mieux fonctionner sur les grandes tables.
Notez que je commence avec Table3
. Il est logique de le faire car, dans votre jeu de résultats final, vous voulez une ligne pour chaque ligne du Table3
. Les left joins
Table3
le jeu de résultats, en dupliquant les lignes Table3
, mais je les regroupe ensuite avec group by
.
La fonction count
n'inclut pas les valeurs null
, et vous pouvez en tirer profit ici: là où les jointures à gauche ne réussissent pas, les valeurs seront null
, il n'est donc pas nécessaire de convertir en valeurs comme "not there".
Quelques points tangentiels
Généralement en SQL, nous n'utilisons pas d'espaces dans les noms de colonnes. Ma réponse a des noms de colonne légèrement différents à votre question, parce que j'ai pris les espaces pour le rendre plus facile à travailler avec les noms de colonnes.
Il peut être utile de donner aux tables des alias significatifs comme je l'ai fait (DOC, USR, CO).
Dans votre code original, ici:
OUTER APPLY ( SELECT TOP 1 Country, Team FROM Table1 WHERE Table1.[Username] = Table2.[Username] ) tNew
vous devez savoir que SQL Server ne garantit pas l'ordre de résultat cohérent, sauf si vous incluez une clause ORDER BY
. Donc, si vous continuiez à utiliser ce morceau de code, à un moment donné dans le futur, la ligne TOP 1
returnnée à l'user abc
pourrait être celle du Canada, pas celle des États-Unis. Je ne suis pas du tout certain que cela vous intéresse, mais cela pourrait être important. En général, il est mauvais d'utiliser TOP 1
comme ceci si vous vous souciez de résultats cohérents. Vous devriez ordonner les résultats, ou utiliser DISTINCT
ou une fonction row_number()
si celles-ci sont plus appropriées dans votre situation.