sql - 从众多中仅选择最便宜或最昂贵的变体

Question

我有一个产品变体表，按产品 key 分组。我还有一个类别表，它与 ProductKey 相连。因此，如果我查询一个类别或多个类别中的产品，我将获得与连接的 ProductKeys 匹配的变体列表。

SELECT Pk, ProductKey, Name, Description, Price
FROM ProductVariant
INNER JOIN Product2Category ON ProductVariant.ProductKey = Product2Category.ProductKey 
INNER JOIN Category ON Product2Category.CategoryKey = Category.pk WHERE Category.pk IN(@categorykeys)

这将导致这样的示例数据

Pk, ProductKey, Name, Description, Price
20, 1, 'Product One - Variant One', 'Description', 25.65
21, 1, 'Product One - Variant Two', 'Description', 24.65
22, 1, 'Product One - Variant Three', 'Description', 29.65
23, 2, 'Product Two - Variant', 'Description', 26.65
24, 2, 'Product Two - Variant', 'Description', 23.65
25, 2, 'Product Two - Variant', 'Description', 25.65

问题是我只想根据 ProductKey 返回一个产品。要么是该组中最便宜的。所以结果会是

Pk, ProductKey, Name, Description, Price
21, 1, 'Product One - Variant Two', 'Description', 24.65
24, 2, 'Product Two - Variant', 'Description', 23.65

还是最贵的。所以结果会是

Pk, ProductKey, Name, Description, Price
22, 1, 'Product One - Variant Three', 'Description', 29.65
23, 2, 'Product Two - Variant', 'Description', 26.65

如何在 SELECT sql 查询中有效地执行此操作？目前，我正在使用 C# 返回所有内容并在服务器的内存中进行过滤。

Answer 1

如果我没理解错的话，您需要使用 ROW_NUMBER() 来获得预期的结果:

表格:

CREATE TABLE Data (
   Pk int, 
   ProductKey int, 
   Name varchar(100), 
   Description varchar(50), 
   Price numeric(10, 2)
)
INSERT INTO Data 
   (Pk, ProductKey, Name, Description, Price)
VALUES   
   (20, 1, 'Product One - Variant One', 'Description', 25.65),
   (21, 1, 'Product One - Variant Two', 'Description', 24.65),
   (22, 1, 'Product One - Variant Three', 'Description', 29.65),
   (23, 2, 'Product Two - Variant', 'Description', 26.65),
   (24, 2, 'Product Two - Variant', 'Description', 23.65),
   (25, 2, 'Product Two - Variant', 'Description', 25.65)

最便宜产品的声明:

SELECT 
   t.Pk, t.ProductKey, t.Name, t.Description, t.Price
FROM (
   SELECT 
     Pk, ProductKey, Name, Description, Price,
     ROW_NUMBER() OVER (PARTITION BY ProductKey ORDER BY Price ASC) AS Rn
   FROM Data
) t
WHERE t.Rn = 1

最昂贵产品的声明:

SELECT 
   t.Pk, t.ProductKey, t.Name, t.Description, t.Price
FROM (
   SELECT 
     Pk, ProductKey, Name, Description, Price,
     ROW_NUMBER() OVER (PARTITION BY ProductKey ORDER BY Price DESC) AS Rn
   FROM Data
) t
WHERE t.Rn = 1

结果:

-------------------------------------------------------------
Pk  ProductKey  Name                        Description Price
-------------------------------------------------------------
21  1           Product One - Variant Two   Description 24.65
24  2           Product Two - Variant       Description 23.65

-------------------------------------------------------------
Pk  ProductKey  Name                        Description Price
-------------------------------------------------------------
22  1           Product One - Variant Three Description 29.65
23  2           Product Two - Variant       Description 26.65