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