gpt4 book ai didi

mysql - SQL查询: Current lowest price of products

转载 作者:行者123 更新时间:2023-11-29 13:40:11 25 4
gpt4 key购买 nike

我正在为项目的 SQL 查询而苦苦挣扎。

给出了下表:

tblProduct => (proProductID, proProductName)
tblSeller => (selSellerID, selSellerName)
linkProductSeller => (linkID, linkProductID, linkSellerID, linkPrice, linkShippingPrice, linkDatetime)

每次卖家的产品价格/运费发生变化时,都会在 linkProductSeller 中添加新行。

我需要了解产品当前的最低价格/运费组合和卖家。

为了获取产品的所有当前价格,我使用此查询

SELECT linkProductID, linkSellerID, linkPrice+linkShippingPrice as price, linkDatetime
FROM linkProductSeller AS a
WHERE linkDatetime = (
SELECT MAX(linkDatetime)
FROM linkProductSeller AS b
WHERE a.linkProductID = b.linkProductID
AND a.linkSellerID = b.linkSellerID)
ORDER BY linkProductID ASC, price ASC, linkDatetime DESC

感谢您的支持。

最佳答案

您需要的所有数据都在 linkProductSeller 中,因此这应该可行

select l.linkProductId, min(linkPrice + linkShippingPrice) as minPrice
from linkProductSeller l,
(select linkProductID, linkSellerId, max(linkDateTime) as linkDateTime
from linkProductSeller
group by linkProductID, linkSellerId) m
where l.linkProductID = m.linkProductID
and l.linkSellerID = m.linkSellerID
and l.linkDateTime = m.linkDateTime
group by linkProductId

关于mysql - SQL查询: Current lowest price of products,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/18181037/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com