MS SQL Server
  1. MS SQL Server
  3. FULL OUTER JOIN dupliquer des lignes à l'aide de COALESCE
Intereting Posts
La requête INSERT montre datatables insérées mais en réalité aucune donnée n'est insérée Comment créer et utiliser une procédure stockée pour SQL-Sever qui va supprimer datatables dans toutes les tables dans une database avec VS 2010? Comment puis-je explorer datatables dans une database SQL, y compris les tables étrangères? Couche d'access aux données Pilote SQL PHP PHP vs Pilote SQL PHP Count () où l'élément compté est un JOIN return à une autre table Insérer dans une table en fonction de l'instruction case … when performance sur colonne xml dans le server sql accorder l'access en lecture sur une vue, mais pas sur ses tables sous-jacentes à partir d'autres bases de données Utilisation des données spatiales Sql (C #) pour find le centre "visuel" des polygones irréguliers Définition d'une valeur de return à partir de la procédure stockée si condition Pourquoi je reçois un résultat différent juste en augmentant une colonne dans la clause Select, comment puis-je résoudre ce problème? Cas SQL donnant un résultat incohérent Comment lire le champ d'image de MSSQL avec PHP Comment sélectionner toutes les dates de 2002 à 2013 dans Microsoft SQL Server 2012

FULL OUTER JOIN dupliquer des lignes à l'aide de COALESCE

Ce fut une longue journée, peut-être que c'est une question simple mais je suis coincé quand même.

Fondamentalement, j'ai deux tables similaires Sales et Forecasts . J'essaie de créer une vue qui sélectionne les lignes des deux tables et sélectionne tout ce qui existe pour un model donné + mois + pays. Si les deux tables contiennent des données, Sales a la priorité, ce qui signifie que les lignes Forecast doivent être omises.

Pour simplifier la requête j'utilise CTE. En fait, le schéma des deux tables est différent et de nombreuses tables sont jointes. Forecasts contient également des lignes d'historique où seul le dernier doit être affiché.

J'ai créé un schéma simplifié et des données pour vous montrer ce que j'essaie de faire: 

 WITH Sales AS ( SELECT ID, Model, Month, Country, Amount = Count, [Forecast / Sales] = 'Sales' FROM dbo.Sales ) , Forecasts AS ( SELECT ID, Model, Month, Country, Amount = Count, [Forecast / Sales] = 'Forecast' FROM dbo.Forecast ) SELECT ID = COALESCE(s.ID, fc.ID), Model = COALESCE(s.Model, fc.Model), Month = COALESCE(s.Month, fc.Month), Country = COALESCE(s.Country, fc.Country), Amount = COALESCE(s.Amount, fc.Amount), [Forecast / Sales] = COALESCE(s.[Forecast / Sales], fc.[Forecast / Sales]) FROM Sales s FULL OUTER JOIN Forecasts fc ON s.Model = fc.Model AND s.Month = fc.Month AND s.Country = fc.Country ORDER BY ID,Month,Country,Model

Voici un sql-violon avec des données d'exemple: http://sqlfiddle.com/#!3/9081b/9/2

Résultat: 

 ID MODEL MONTH COUNTRY AMOUNT FORECAST / SALES 1 ABC December, 01 2013 00:00:00+0000 Germany 777 Sales 2 ABC January, 01 2014 00:00:00+0000 Germany 999 Sales 3 ABC February, 01 2014 00:00:00+0000 Germany 900 Sales 3 ABC February, 01 2014 00:00:00+0000 Germany 900 Sales 4 ABC January, 01 2014 00:00:00+0000 UK 600 Forecast 4 ABC February, 01 2014 00:00:00+0000 UK 444 Sales 5 ABC March, 01 2014 00:00:00+0000 UK 500 Forecast

Cette requête renvoie des duplicates en fonction de l' ID et de la source (dernière colonne). 

 3 ABC February, 01 2014 00:00:00+0000 Germany 900 Sales 3 ABC February, 01 2014 00:00:00+0000 Germany 900 Sales

Apparemment, les lignes Sales sont en cours de duplication à plusieurs niveaux de Forecast pour cette combinaison model + mois + pays. Comment puis-je get uniquement des lignes Sales si les lignes Sales + Forecast sont disponibles sans duplicates et Forecast s'il n'y a pas de lignes Sales ?

La réponse de Lamak fournit la raison des lignes dupliquées dans le résultat. Voici une solution: 

 WITH Sales AS ( ... ) , Forecasts AS ( ...) , Combos AS -- get all distinct ( -- model + month + country SELECT Model, Month, Country -- combinations FROM Sales -- from Sales UNION -- this is UNION DISTINCT SELECT Model, Month, Country FROM Forecasts -- and Forecasts ) SELECT ID = COALESCE(s.ID, f.ID), c.Model, c.Month, c.Country, Amount = COALESCE(s.Amount, f.Amount), [Forecast / Sales] = COALESCE(s.[Forecast / Sales], f.[Forecast / Sales]) FROM Combos c LEFT JOIN Sales s ON s.Model = c.Model AND s.Month = c.Month AND s.Country = c.Country LEFT JOIN Forecasts f ON s.Model IS NULL -- join Forecasts only if there is no Sales AND f.Model = c.Model AND f.Month = c.Month AND f.Country = c.Country ORDER BY ID, Month, Country, Model ;

Test chez: SQL-Fiddle

Le problème avec votre requête n'est pas l'utilisation de COALESCE , mais simplement avec le JOIN . Il y a 2 lignes dans le tableau Forecast qui ont la même combinaison de Model, Month, Country , lignes avec ID 2 et 3: 

 ╔════╦═══════╦═════════════════════════╦═════════╦═══════╗ ║ ID ║ Model ║ Month ║ Country ║ Count ║ ╠════╬═══════╬═════════════════════════╬═════════╬═══════╣ ║ 2 ║ ABC ║ 2014-02-01 00:00:00.000 ║ Germany ║ 1100 ║ ║ 3 ║ ABC ║ 2014-02-01 00:00:00.000 ║ Germany ║ 900 ║ ╚════╩═══════╩═════════════════════════╩═════════╩═══════╝

Les deux rejoignent l' ID ligne 3 de la table Sales : 

 ╔════╦═══════╦═════════════════════════╦═════════╦═══════╗ ║ ID ║ Model ║ Month ║ Country ║ Count ║ ╠════╬═══════╬═════════════════════════╬═════════╬═══════╣ ║ 3 ║ ABC ║ 2014-02-01 00:00:00.000 ║ Germany ║ 900 ║ ╚════╩═══════╩═════════════════════════╩═════════╩═══════╝

Et puisque votre requête utilise COALESCE(s.ID, fc.ID) , alors vous obtenez 2 lignes avec ID 3 dans les résultats

Il semble que vous souhaitiez simplement renvoyer l'intégralité de l'set de Sales et le compléter avec des inputs de Forecasts qui ne figurent pas dans Sales . Pour cela, j'utiliserais probablement UNION ALL comme ceci : 

 WITH Sales AS ( ... ) , Forecasts AS ( ... ) SELECT ID, Model, Month, Country, Amount, [Forecast / Sales] FROM Sales UNION ALL SELECT ID, Model, Month, Country, Amount, [Forecast / Sales] FROM Forecasts WHERE NOT EXISTS ( SELECT Model, Month, Country INTERSECT SELECT Model, Month, Country FROM Sales );