我将需要翻译的内容进行了翻译,涉及到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+ 的要求。