MS SQL Server
  1. MS SQL Server
  3. Valeur différente de SQL SUM uniquement
Intereting Posts
SQL Meilleure performance: char (10) et sortingm ou varchar (10) Migrations automatiques plusieurs services une database MS Access à SQL Server dans C # .NET Comment split une string et insert des valeurs dans une table dans SQL Server Colonnes de date dans SQL Server (MSSQL-JDBC 3.0) fonctionnant sous Java 1.7.0 récupérées comme 2 jours dans le passé procédure stockée sql – plusieurs users créent une table temporaire en même time Comment remplir une date dans SQL Server 2008 R2 à l'aide de powershell 3.0? Erreur lors de la restauration du file .bak sur SQL Server 2008 Comment join sur deux champs où l'on pourrait être nul Vérifiez si la colonne d'image est null SQL Server: cas de clause WHERE avec entre Appels SP «moqueurs» (à partir d'autres SP) lors de l'utilisation de TST DATE HEURE différence entre 2 dates Supprimer tous les index de la database spécifique Insérez des données volumineuses dans SQL Server via c # dans un seul appel de database

Valeur différente de SQL SUM uniquement

J'ai cette requête: 

SELECT * FROM ( SELECT u.*, c1.FAME AS Fame1, c2.FAME AS Fame2, c3.FAME AS Fame3, c4.FAME AS Fame4 FROM UserInfo_1 AS u LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) UNION SELECT u.*, c1.FAME AS Fame1, c2.FAME AS Fame2, c3.FAME AS Fame3, c4.FAME AS Fame4 FROM UserInfo_2 AS u LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) UNION SELECT u.*, c1.FAME AS Fame1, c2.FAME AS Fame2, c3.FAME AS Fame3, c4.FAME AS Fame4 FROM UserInfo_3 AS u LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) UNION SELECT u.*, c1.FAME AS Fame1, c2.FAME AS Fame2, c3.FAME AS Fame3, c4.FAME AS Fame4 FROM UserInfo_4 AS u LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) ) AS a WHERE UID IN (378358,378359)

Le résultat: 

 UID Char1 Char2 Char3 Fame1 Fame2 Fame3 Fame4 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 378358 46497 130 40365 NULL 10 8839 NULL 378358 46497 130 40365 NULL 2470 8839 NULL 378359 36935 121 123 NULL 17285 NULL 292 378359 36935 121 123 NULL 17285 NULL 1277

Comme vous pouvez voir le résultat ci-dessus, toutes datatables sont identiques à l'exception des colonnes Fame4 pour UID=378359 et Fame2 pour UID=378358 .

La question est comment puis-je SUMMER les différentes valeurs (seulement dans les colonnes Fame) SEULEMENT et returnner en tant que 1 ligne pour chaque UID .

le résultat souhaité: 

 UID Char1 Char2 Char3 Fame1 Fame2 Fame3 Fame4 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 378358 46497 130 40365 NULL 2480 8839 NULL 378359 36935 121 123 NULL 17285 NULL 1569

Si vous avez besoin de SUM uniquement des valeurs différentes utilisent SUM(DISTINCT columnname) : 

 SELECT UID, Char1, Char2, Char3, SUM(DISTINCT Fame1) Fame1, SUM(DISTINCT Fame2) Fame2, SUM(DISTINCT Fame3) Fame3, SUM(DISTINCT Fame4) Fame4 FROM ( --Here comes your sub-query ) AS a WHERE UID IN (378358,378359) GROUP BY UID, Char1, Char2, Char3,

Utiliser le groupe par : 

 SELECT a.UID,a.Char1,a.Char2,a.Char3, SUM(a.Fame1), SUM(a.Fame2), SUM(a.Fame3), SUM(a.Fame4) FROM ( SELECT u.*, ISNULL(c1.FAME,0) AS Fame1, ISNULL(c2.FAME,0) AS Fame2, ISNULL(c3.FAME,0) AS Fame3, ISNULL(c4.FAME,0) AS Fame4 FROM UserInfo_1 AS u LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) UNION SELECT u.*, ISNULL(c1.FAME,0) AS Fame1, ISNULL(c2.FAME,0) AS Fame2, ISNULL(c3.FAME,0) AS Fame3, ISNULL(c4.FAME,0) AS Fame4 FROM UserInfo_2 AS u LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) UNION SELECT u.*, ISNULL(c1.FAME,0) AS Fame1, ISNULL(c2.FAME,0) AS Fame2, ISNULL(c3.FAME,0) AS Fame3, ISNULL(c4.FAME,0) AS Fame4 FROM UserInfo_3 AS u LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) UNION SELECT u.*, ISNULL(c1.FAME,0) AS Fame1, ISNULL(c2.FAME,0) AS Fame2, ISNULL(c3.FAME,0) AS Fame3, ISNULL(c4.FAME,0) AS Fame4 FROM UserInfo_4 AS u LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) ) AS a WHERE UID IN (378358,378359) GROUP By a.UID,a.Char1,a.Char2,a.Char3 
 SELECT a.UID,a.Char1,a.Char2,a.Char3, a.Fame1, SUM(a.Fame2) AS Fame2, a.Fame3, SUM(a.Fame4) AS Fame4 FROM ( SELECT u.*, ISNULL(c1.FAME,0) AS Fame1, ISNULL(c2.FAME,0) AS Fame2, ISNULL(c3.FAME,0) AS Fame3, ISNULL(c4.FAME,0) AS Fame4 FROM UserInfo_1 AS u LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) UNION SELECT u.*, ISNULL(c1.FAME,0) AS Fame1, ISNULL(c2.FAME,0) AS Fame2, ISNULL(c3.FAME,0) AS Fame3, ISNULL(c4.FAME,0) AS Fame4 FROM UserInfo_2 AS u LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) UNION SELECT u.*, ISNULL(c1.FAME,0) AS Fame1, ISNULL(c2.FAME,0) AS Fame2, ISNULL(c3.FAME,0) AS Fame3, ISNULL(c4.FAME,0) AS Fame4 FROM UserInfo_3 AS u LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) UNION SELECT u.*, ISNULL(c1.FAME,0) AS Fame1, ISNULL(c2.FAME,0) AS Fame2, ISNULL(c3.FAME,0) AS Fame3, ISNULL(c4.FAME,0) AS Fame4 FROM UserInfo_4 AS u LEFT JOIN CharInfo_1 AS c1 ON c1.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_2 AS c2 ON c2.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_3 AS c3 ON c3.UID IN (u.Char1, u.Char2, u.Char3) LEFT JOIN CharInfo_4 AS c4 ON c4.UID IN (u.Char1, u.Char2, u.Char3) ) AS a WHERE UID IN (378358,378359) GROUP By a.UID,a.Char1,a.Char2,a.Char3,a.Fame1,a.Fame3