如何获得每家公司每种产品类型最便宜的产品？
如何获得每家公司每种产品类型最便宜的产品？
提问于 2018-11-29 13:00:27
我以前使用以下方法列出了有效的报价：
SELECT
pt.prod_type_description 'Tipo do Produto',
p.prod_brand 'Marca',
c.comp_fantasy_name 'Nome da Empresa',
o.offer_price 'Preco'
FROM offer AS o
INNER JOIN company AS c ON o.comp_id = c.comp_id
INNER JOIN product AS p ON o.prod_id = p.prod_id
INNER JOIN product_type AS pt ON p.prod_type_id = pt.prod_type_id
WHERE
CONVERT(VARCHAR, o.offer_expired_at, 23) >= CONVERT(VARCHAR, dbo.datetimeBrasilia(), 23)
GROUP BY pt.prod_type_id, o.offer_price, p.prod_id, c.comp_fantasy_name, pt.prod_type_description, p.prod_brand, o.offer_id但我的问题是，如何获得每家公司每种产品的最便宜的产品(而不是品牌)？
TYPE PRODUCT | BRAND | COMPANY | PRICE
----------------------------------------------------------
Cimento Cauê MAConstuc 13.65
Areia AB Areias MAConstuc 15.79
Brita Briforte MAConstuc 21.30
Paver PaviS MotriX MAConstuc 0.65
Paver Raquete Forten TilJola 1.20
Cimento Votoran TilJola 10.40
Cimento Cauê TilJola 14.65
Areia AB Areias TilJola 11.56
Brita Briforte TilJola 9.99
Paver PaviS MotriX TilJola 1.30
Paver Raquete Forten TilJola 1.00
Cimento Votoran KiCalc 22.90
Cimento Cauê KiCalc 20.00
Areia AB Areias KiCalc 18.30
Brita Briforte KiCalc 17.00
Paver PaviS MotriX KiCalc 1.35
Paver Raquete Forten KiCalc 0.99
-- example of company TilJola (without Cimento Cauê)
Cimento Votoran TilJola 10.40
Areia AB Areias TilJola 11.56
Paver PaviS MotriX TilJola 1.30
Paver Raquete Forten TilJola 1.00
-- example of company KiCalc (without Cimento Votoran)
Cimento Cauê KiCalc 20.00
Areia AB Areias KiCalc 18.30
Brita Briforte KiCalc 17.00
Paver PaviS MotriX KiCalc 1.35
Paver Raquete Forten KiCalc 0.99
在此之前，我已经使用以下方法列出了有效的报价：
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-11-29 13:05:10
给定查询的这种结构，最简单的方法是order by中带有top (1) with ties的窗口函数。
SELECT TOP (1) WITH TIES
pt.prod_type_description as [Tipo do Produto],
p.prod_brand as Marca,
c.comp_fantasy_name as [Nome da Empresa],
o.offer_price as Preco
FROM offer o JOIN
company c
ON o.comp_id = c.comp_id JOIN
product p
ON o.prod_id = p.prod_id JOIN
product_type pt
ON p.prod_type_id = pt.prod_type_id
WHERE offer_expired_at >= dbo.datetimeBrasilia()
GROUP BY pt.prod_type_id, o.offer_price, p.prod_id, c.comp_fantasy_name, pt.prod_type_description, p.prod_brand, o.offer_id
ORDER BY ROW_NUMBER() OVER (PARTITION BY pt.prod_type_description, pt.comp_fantasy_name ORDER BY o.offer_price);注意到查询中的其他一些更改：
- 日期应该作为日期而不是字符串进行比较。
- 在Server中，不应使用没有长度的
VARCHAR。长度因上下文的不同而变化，而由于没有长度而导致的修复问题可能真的很难调试。 - 仅对字符串和日期常量使用单引号。如果你开始用它们作为列名，你最终会犯一个错误。
- 我只对列别名使用
as，而不对表别名使用。诚然，这只是一种偏好，但我认为它将错误的可能性降到最低(特别是在select中去掉一个逗号)。
Stack Overflow用户
发布于 2018-11-29 13:03:20
您可以尝试使用row_number()函数
select * from
(
SELECT
pt.prod_type_description 'Tipo do Produto',
p.prod_brand 'Marca',
c.comp_fantasy_name 'Nome da Empresa',
o.offer_price 'Preco',
row_number() over(partition by c.comp_fantasy_name,pt.prod_type_description order by o.offer_price) as rn
FROM offer AS o
INNER JOIN company AS c ON o.comp_id = c.comp_id
INNER JOIN product AS p ON o.prod_id = p.prod_id
INNER JOIN product_type AS pt ON p.prod_type_id = pt.prod_type_id
WHERE
CONVERT(VARCHAR, o.offer_expired_at, 23) >= CONVERT(VARCHAR, dbo.datetimeBrasilia(), 23)
)A where rn=1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53539635
复制相关文章
相似问题
腾讯云开发者
