T-Sql选择和分组MIN()

Question

我将需要翻译的内容进行了翻译，涉及到it技术。

我有三个表：

产品类别 [1 - m] 产品 [1-m] 产品价格

一个简单的脚本如下：

select pc.CategoryId ,pp.LanguageId , pp.ProductId ,pp.Price
from ProductCategory as pc
    inner join Product as p on pc.ProductId = p.Id
    inner join ProductPrice as pp on p.Id = pp.ProductId
order by CategoryId , LanguageId , ProductId

展示这些表格数据：

CategoryId  LanguageId  ProductId   Price
----------- ----------- ----------- ---------------------------------------
1           1           1           55.00
1           1           2           55.00
1           2           1           66.00
1           2           2           42.00
2           1           3           76.00
2           1           4           32.00
2           2           3           89.00
2           2           4           65.00
4           1           4           32.00
4           1           5           77.00
4           2           4           65.00
4           2           5           85.00

现在我需要的是： 对于每个类别，获取完整行，但仅包含最低价格的产品。

我刚刚写了一个简单的查询，像这样：

with dbData as
(
select pc.CategoryId ,pp.LanguageId , pp.ProductId ,pp.Price
from ProductCategory as pc
    inner join Product as p on pc.ProductId = p.Id
    inner join ProductPrice as pp on p.Id = pp.ProductId
)
select distinct db1.*
from dbData as db1
inner join dbData as db2 on db1.CategoryId = db2.CategoryId
where db1.LanguageId = db2.LanguageId
    and db1.Price = (select Min(Price) 
                        from dbData 
                        where CategoryId = db2.CategoryId
                                and LanguageId = db2.LanguageId)

并且它的结果是正确的：

CategoryId  LanguageId  ProductId   Price
----------- ----------- ----------- ---------------------------------------
1           1           1           55.00
1           1           2           55.00
1           2           2           42.00
2           1           4           32.00
2           2           4           65.00
4           1           4           32.00
4           2           4           65.00

有没有更酷的方法来完成这个操作？

注意：查询必须符合 Sql-Server 2008 R2+ 的要求。

- Ermir Beqiraj

Answer 1

您可以使用窗口函数例如 RANK()：

WITH cte AS 
(
  select pc.CategoryId, pp.LanguageId, pp.ProductId, pp.Price,
    rnk = RANK() OVER(PARTITION BY pc.CategoryId ,pp.LanguageId ORDER BY pp.Price) 
  from ProductCategory as pc
  join Product as p on pc.ProductId = p.Id
  join ProductPrice as pp on p.Id = pp.ProductId
)
SELECT CategoryId, LanguageId, ProductId, Price
FROM cte
WHERE rnk = 1;

实时演示

- Lukasz Szozda

Answer 2

如果你需要按categoryid和languageid获取产品价格，你可以将languageid添加到分区中。

select top 1 with ties pc.CategoryId ,pp.LanguageId , pp.ProductId ,pp.Price
    from ProductCategory as pc
    inner join Product as p on pc.ProductId = p.Id
    inner join ProductPrice as pp on p.Id = pp.ProductId
        order by row_number() over  (partition by pc.categoryid order by price)

- TheGameiswar

Answer 3

你的查询中没有使用Product表，所以似乎不是必要的。我会这样写：

select ppc.*
from (select pc.CategoryId, pp.LanguageId , pp.ProductId, pp.Price,
             row_number() over (partition by pc.CategoryId order by pp.Price) as seqnum
      from ProductCategory pc inner join
           ProductPrice pp
           on pc.ProductId = pp.ProductId
     ) ppc
where seqnum = 1
order by CategoryId, LanguageId, ProductId;

- Gordon Linoff