MS SQL Server
  1. MS SQL Server
  3. SQL – Comment agréger et limiter les résultats à une ligne par ID en fonction de l'agrégat
Intereting Posts
Package SSIS prenant des âges pour modifier les composants Comment mon application peut-elle utiliser à la fois une database MS Access et une database SQL Server en même time? Comment utiliser la requête SQL avec le nombre de havng ici? Comment get des informations sur l'user à partir de ASPNETDB avec des procédures stockées Filtrer les noms de bases de données dans le server SQL à l'aide du GUID Comment créer plusieurs lignes à partir d'une seule ligne Le programme d'installation de SQL Server échoue avec "Impossible de générer une class temporaire" lorsque le chef-solo sur le vagrant Comment faire reference à une image en pièce jointe Récupération des lignes enregistrées avec encryptbypassphrase MSSQL – Vérifier où aucune autre valeur n'existe Où puis-je get un file anglais Thesaurus pour SQL Server 2008? Obtention de la 'date' d'un champ DateTime dans SQL Server Ajouter un titre d'en-tête au-dessus du résultat de la requête dans SQL Server Transaction SQL incommittable lors de l'utilisation de try..catch .. Pourquoi? Détecter par programme SQL Server Edition

SQL – Comment agréger et limiter les résultats à une ligne par ID en fonction de l'agrégat

J'ai essayé de rassembler une requête SQL pendant quelques heures et je n'arrive pas à la comprendre. Considérez les exemples de arrays suivants: ProductCategories and ProductCategories : 

 Products -------- ProductId ProductName --------- ----------- 1 | Achilles 2 | Hermes 3 | Apollo 4 | Zeus 5 | Poseidon 6 | Eros ProductCategories ----------------- ProductId Category --------- -------- 1 | Wars 1 | Wars|Trojan 1 | Wars|Trojans|Mortals 1 | Toys|Games 2 | Travel 2 | Travel|Trade 2 | Communication|Language|Writing 5 | Oceanware 6 | Love 6 | Love|Candy 6 | Love|Valentines 3 | Sunshine 4 | Lightning

L'objective est de sélectionner l'ID du produit, le nom du produit et l'une des catégories associées au produit, de sorte que chaque ID / nom de produit apparaisse une fois dans les résultats et que la catégorie sélectionnée soit celle qui contient le plus de caractères. Dans le cas où 2 catégories (ou plus) pour un produit sont à égalité pour le plus grand nombre de pipes, le choix random de l'une d'entre elles fonctionnera.

En d'autres termes, la requête devrait aboutir à cet set de données: 

 ProductId ProductName Category --------- ----------- -------- 1 | Achilles | Wars|Trojans|Mortals 2 | Hermes | Communication|Language|Writing 3 | Apollo | Sunshine 4 | Zeus | Lightning 5 | Poseidon | Oceanware 6 | Eros | Love|Valentines

(Remarque, la catégorie returnnée pour Eros pourrait aussi être Love | Candy, soit acceptable)

A partir de maintenant, j'ai ce SQL, qui ne fonctionne évidemment pas car il returnne une ligne pour chaque combinaison produit / catégorie, pas seulement la catégorie avec le plus de pipes: 

 SELECT ProductId, ProductName, Category, MAX(PipeCount) FROM ( SELECT DISTINCT p.ProductId AS ProductId, p.ProductName AS ProductName, c.Category AS Category, LEN(c.CategoryName) - LEN(REPLACE(c.CategoryName, '|', '')) AS PipeCount FROM Products p INNER JOIN ProductCategories c ON p.ProductId = c.ProductId ) Subquery GROUP BY ProductId, ProductName, Category, PipeCount

Cependant, je n'arrive pas à get ma requête plus près que cela. Je devais returnner seulement la ligne pour chaque produit où le PipeCount est le PipeCount maximum pour n'importe quelle rangée pour le produit. Toute aide serait appréciée. S'il vous plaît noter que ce ne sont pas mes données réelles; c'est beaucoup plus compliqué que cela, mais cet exemple devrait suffire. Je travaille sur SQL Server 2012, mais j'espère qu'une bonne réponse serait compatible avec pratiquement n'importe quelle version de SQL.

Vous pouvez utiliser ROW_NUMBER pour get ROW_NUMBER avec le plus grand nombre de CategoryName :

SQL Fiddle 

 SELECT p.*, pc.CategoryName FROM Products p INNER JOIN( SELECT *, RN = ROW_NUMBER() OVER(PARTITION BY ProductId ORDER BY LEN(CategoryName) - LEN(REPLACE(CategoryName, '|', '')) DESC) FROM ProductCategories ) pc ON pc.ProductId = p.ProductId WHERE RN = 1

Voici la solution utilisant ROW_NUMBER 

 --CTE as data sample for two tables ; WITH Products AS ( SELECT * FROM ( VALUES ( 1, 'Achilles'), ( 2, 'Hermes'), ( 3, 'Apollo'), ( 4, 'Zeus'), ( 5, 'Poseidon'), ( 6, 'Eros') ) AS t ( ProductId, ProductName ) ), ProductCategories AS ( SELECT * FROM ( VALUES ( 1 , 'Wars'), ( 1 , 'Wars|Trojan'), ( 1 , 'Wars|Trojans|Mortals'), ( 1 , 'Toys|Games'), ( 2 , 'Travel'), ( 2 , 'Travel|Trade'), ( 2 , 'Communication|Language|Writing'), ( 5 , 'Oceanware'), ( 6 , 'Love'), ( 6 , 'Love|Candy'), ( 6 , 'Love|Valentines'), ( 3 , 'Sunshine'), ( 4 , 'Lightning') ) AS T ( ProductId, CategoryName ) ) --Final Query SELECT T.ProductId , T.ProductName , T.CategoryName FROM ( SELECT P.ProductID , P.ProductName , C.CategoryName , LEN(C.CategoryName) - LEN(REPLACE(C.CategoryName, '|', '')) AS Pipes , ROW_NUMBER() OVER ( PARTITION BY P.ProductID ORDER BY LEN(C.CategoryName) - LEN(REPLACE(C.CategoryName, '|', '')) DESC, LEN(C.CategoryName) DESC ) AS RN FROM Products AS P JOIN ProductCategories AS C ON P.ProductId = C.ProductId ) AS T WHERE T.RN = 1

J'ai fini par résoudre le problème en utilisant diverses sous-requêtes. Une mise en garde est que cela dépend de la table ProductCategories dans mon exemple ayant une colonne unique que je n'ai pas explicitement spécifiée. Dans mes données réelles, cette colonne existe déjà, mais face à un problème similaire, on pourrait append une telle colonne pour faire fonctionner cette solution. Voici le SQL: 

 SELECT Sub1.ProductId, Sub3.Category FROM ( SELECT o.ProductId AS ProductId, MAX(LEN(REPLACE(c.Category, '|', '||')) - LEN(c.Category)) AS MaxPipeCount FROM Products o INNER JOIN ProductCategories c ON o.ProductId = c.ProductId GROUP BY o.ProductID ) Sub1 INNER JOIN ( SELECT o.ProductId AS ProductId, LEN(REPLACE(c.Category, '|', '||')) - LEN(c.Category) AS PipeCount, MAX(c.UniqueId) AS MaxUniqueId FROM Products o INNER JOIN ProductCategories c ON o.ProductId = c.ProductId GROUP BY o.ProductID, LEN(REPLACE(c.Category, '|', '||')) - LEN(c.Category) ) Sub2 ON Sub1.MaxPipeCount = Sub2.PipeCount AND Sub1.ProductId = Sub2.ProductId INNER JOIN ( SELECT DISTINCT o.ProductId, c.Category, LEN(REPLACE(c.Category, '|', '||')) - LEN(c.Category) AS PipeCount, c.UniqueId FROM Products o INNER JOIN ProductCategories c ON o.ProductId = c.ProductId ) Sub3 ON Sub1.MaxPipeCount = Sub3.PipeCount AND Sub2.MaxUniqueId = Sub3.UniqueId AND Sub1.ProductId = Sub3.ProductId