MS SQL Server
  1. MS SQL Server
  3. SQL – comment GROUP par ID et identifier la colonne avec la valeur la plus élevée?
Intereting Posts
SQL – Retourne toutes les lignes même si count est nul pour l'item SQL Server – Insertion sélective de champs dans la table temporaire Gestion des connections de database dans .NET – Garder une connection en vie SQL Server: comment ignorer l'intégrité référentielle jusqu'à COMMIT? Comment contourner les types de champs entiers non signés non pris en charge dans MS SQL? Les fonctions fenêtrées ne peuvent apparaître que dans les clauses SELECT ou ORDER BY Migration de Entity Framework 6 Code First MSSQL à MySQL comment empêcher une transaction de revenir en arrière lorsqu'une erreur est détectée dans try catch in sql server 2005 Comment BIG faites-vous votre Nvarchar () SQL Server: requête UNPIVOT pour get des résultats comme indiqué ci-dessous? Insérer dans une table en sélectionnant à partir d'une procédure stockée Autorisation CREATE DATABASE refusée dans la database «maître». SQL Server 2012 SQL Server Création de files CSV problématiques Ordre SQL Server par date, puis groupe, mais groupe répétitif possible SQL pour join la colonne nvarchar (max) avec la colonne int

SQL – comment GROUP par ID et identifier la colonne avec la valeur la plus élevée?

J'ai un défi SQL avec lequel j'ai besoin d'aide.

Voici un exemple simplifié, dans mon cas réel, j'ai environ 500k lignes dans une vue lente. Donc, si vous avez une solution efficace, je l'apprécierais. Je pense que je dois utiliser GROUP BY d'une manière ou d'une autre, mais je ne suis pas sûr.

Disons que j'ai une table comme celle-ci 

╔═════════╦══════════╦══════════╦═══════╗ ║ ORDERID ║ NAME ║ TYPE ║ PRICE ║ ╠═════════╬══════════╬══════════╬═══════╣ ║ 1 ║ Broccoli ║ Food ║ 1 ║ ║ 1 ║ Beer ║ Beverage ║ 5 ║ ║ 1 ║ Coke ║ Beverage ║ 2 ║ ║ 2 ║ Beef ║ Food ║ 2.5 ║ ║ 2 ║ Juice ║ Beverage ║ 1.5 ║ ║ 3 ║ Beer ║ Beverage ║ 5 ║ ║ 4 ║ Tomato ║ Food ║ 1 ║ ║ 4 ║ Apple ║ Food ║ 1 ║ ║ 4 ║ Broccoli ║ Food ║ 1 ║ ╚═════════╩══════════╩══════════╩═══════╝

Donc ce que je veux faire est:

Dans chaque command, où il y a la command line de nourriture et de boisson, je veux le prix de boisson le plus élevé

Donc, dans cet exemple, je voudrais avoir un set de résultats de ceci: 

 ╔═════════╦═══════╦═══════╗ ║ ORDERID ║ NAME ║ PRICE ║ ╠═════════╬═══════╬═══════╣ ║ 1 ║ Beer ║ 5 ║ ║ 2 ║ Juice ║ 1.5 ║ ╚═════════╩═══════╩═══════╝

Comment puis-je y parvenir d'une manière efficace?

Puisque vous avez étiqueté SQL Server , utilisez l' Common Table Expression et les Window Functions . 

 ;WITH filteredList AS ( SELECT OrderID FROM tableName WHERE Type IN ('Food','Beverage') GROUP BY OrderID HAVING COUNT(DISTINCT Type) = 2 ), greatestList AS ( SELECT a.OrderID, a.Name, a.Type, a.Price, DENSE_RANK() OVER (PARTITION BY a.OrderID ORDER BY a.Price DESC) rn FROM tableName a INNER JOIN filteredList b ON a.OrderID = b.OrderID WHERE a.Type = 'Beverage' ) SELECT OrderID, Name, Type, Price FROM greatestList WHERE rn = 1
  • SQLFiddle Demo

Vous pouvez utiliser la sous-requête qui obtient le max(price) pour chaque command avec à la fois la nourriture et la boisson, puis la joindre à votre table pour get le résultat: 

 select t1.orderid, t1.name, t1.price from yourtable t1 inner join ( select max(price) MaxPrice, orderid from yourtable t where type = 'Beverage' and exists (select orderid from yourtable o where type in ('Food', 'Beverage') and t.orderid = o.orderid group by orderid having count(distinct type) = 2) group by orderid ) t2 on t1.orderid = t2.orderid and t1.price = t2.MaxPrice

Voir SQL Fiddle avec démo

Le résultat est: 

 | ORDERID | NAME | PRICE | --------------------------- | 1 | Beer | 5 | | 2 | Juice | 1.5 |

C'est la division relationnelle: lien 1 , lien 2 .

Si la table du diviseur (uniquement la nourriture et les boissons ) est statique, vous pouvez utiliser l'une des solutions suivantes: 

 DECLARE @OrderDetail TABLE ([OrderID] int, [Name] varchar(8), [Type] varchar(8), [Price] decimal(10,2)) ; INSERT INTO @OrderDetail ([OrderID], [Name], [Type], [Price]) SELECT 1, 'Broccoli', 'Food', 1.0 UNION ALL SELECT 1, 'Beer', 'Beverage', 5.0 UNION ALL SELECT 1, 'Coke', 'Beverage', 2.0 UNION ALL SELECT 2, 'Beef', 'Food', 2.5 UNION ALL SELECT 2, 'Juice', 'Beverage', 1.5 UNION ALL SELECT 3, 'Beer', 'Beverage', 5.0 UNION ALL SELECT 4, 'Tomato', 'Food', 1.0 UNION ALL SELECT 4, 'Apple', 'Food', 1.0 UNION ALL SELECT 4, 'Broccoli', 'Food', 1.0 -- Solution 1 SELECT od.OrderID, COUNT(DISTINCT od.Type) AS DistinctTypeCount, MAX(CASE WHEN od.Type='beverage' THEn od.Price END) AS MaxBeveragePrice FROM @OrderDetail od WHERE od.Type IN ('food', 'beverage') GROUP BY od.OrderID HAVING COUNT(DISTINCT od.Type) = 2 -- 'food' & 'beverage' -- Solution 2: better performance SELECT pvt.OrderID, pvt.food AS MaxFoodPrice, pvt.beverage AS MaxBeveragePrice FROM ( SELECT od.OrderID, od.Type, od.Price FROM @OrderDetail od WHERE od.Type IN ('food', 'beverage') ) src PIVOT ( MAX(src.Price) FOR src.Type IN ([food], [beverage]) ) pvt WHERE pvt.food IS NOT NULL AND pvt.beverage IS NOT NULL

Résultats (pour les solutions 1 et 2): 

 OrderID DistinctTypeCount MaxBeveragePrice ----------- ----------------- --------------------------------------- 1 2 5.00 2 2 1.50 Table 'Worktable'. Scan count 2, logical reads 23, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#09DE7BCC'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. OrderID MaxFoodPrice MaxBeveragePrice ----------- --------------------------------------- --------------------------------------- 1 1.00 5.00 2 2.50 1.50 Table '#09DE7BCC'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Si vous utilisez Sql-Server 2005 ou supérieur, vous pouvez utiliser une fonction CTE avec DENSE_RANK : 

 WITH CTE AS (SELECT orderid, name, type, price, RN = Dense_rank() OVER ( PARTITION BY orderid ORDER BY CASE WHEN type='Beverage' THEN 0 ELSE 1 END ASC , price DESC) FROM dbo.tablename t WHERE EXISTS(SELECT 1 FROM dbo.tablename t2 WHERE t2.orderid = t.orderid AND type = 'Food') AND EXISTS(SELECT 1 FROM dbo.tablename t2 WHERE t2.orderid = t.orderid AND type = 'Beverage')) SELECT orderid, name, price FROM CTE WHERE rn = 1

Utilisez DENSE_RANK si vous souhaitez que toutes les commands DENSE_RANK le même prix et ROW_NUMBER si vous le souhaitez.

DEMO