在 SQL 中选择每组最少行

发布于 2024-11-26 15:07:10 字数 392 浏览 1 评论 0原文

我正在尝试选择每个条件类别的最低价格。我做了一些搜索并编写了下面的代码。但是，所选字段显示为空。有什么解决办法吗？

SELECT Sales.Sale_ID, Sales.Sale_Price, Sales.Condition
FROM Items
LEFT JOIN Sales ON ( Items.Item_ID = Sales.Item_ID
AND Sales.Expires_DateTime > NOW( )
AND Sales.Sale_Price = (
SELECT MIN( s2.Sale_Price )
FROM Sales s2
WHERE Sales.`Condition` = s2.`Condition` ) )
WHERE Items.ISBN =9780077225957

原文

I am trying to select the min price of each condition category. I did some search and wrote the code below. However, it shows null for the selected fields. Any solution?

SELECT Sales.Sale_ID, Sales.Sale_Price, Sales.Condition
FROM Items
LEFT JOIN Sales ON ( Items.Item_ID = Sales.Item_ID
AND Sales.Expires_DateTime > NOW( )
AND Sales.Sale_Price = (
SELECT MIN( s2.Sale_Price )
FROM Sales s2
WHERE Sales.`Condition` = s2.`Condition` ) )
WHERE Items.ISBN =9780077225957

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

海的爱人是光 2024-12-03 15:07:10

下面是一个稍微复杂一点的解决方案，但其中包含您的 Sale_ID。

SELECT TOP 1 Sale_Price, Sale_ID, Condition
FROM Sales
WHERE Sale_Price IN (SELECT MIN(Sale_Price) 
                     FROM Sales 
                     WHERE 
                     Expires_DateTime > NOW() 
                     AND 
                       Item_ID IN     
                         (SELECT Item_ID FROM Items WHERE ISBN = 9780077225957) 
                     GROUP BY Condition )

如果超过 1 笔销售具有相同的最低价格，而您只想退回一件，则显示“TOP 1”。

（内部查询直接取自@Michael Ames 的回答）

A little more complicated solution, but one that includes your Sale_ID is below.

SELECT TOP 1 Sale_Price, Sale_ID, Condition
FROM Sales
WHERE Sale_Price IN (SELECT MIN(Sale_Price) 
                     FROM Sales 
                     WHERE 
                     Expires_DateTime > NOW() 
                     AND 
                       Item_ID IN     
                         (SELECT Item_ID FROM Items WHERE ISBN = 9780077225957) 
                     GROUP BY Condition )

The 'TOP 1' is there in case more than 1 sale had the same minimum price and you only wanted one returned.

(internal query taken directly from @Michael Ames answer)

回复收藏 0 原文

小兔几 2024-12-03 15:07:10

如果您不需要 Sales.Sale_ID，此解决方案更简单：

SELECT MIN(Sale_Price), Condition
FROM Sales
WHERE Expires_DateTime > NOW()
AND Item_ID IN
    (SELECT Item_ID FROM Items WHERE ISBN = 9780077225957)
GROUP BY Condition

祝您好运！

If you don't need Sales.Sale_ID, this solution is simpler:

SELECT MIN(Sale_Price), Condition
FROM Sales
WHERE Expires_DateTime > NOW()
AND Item_ID IN
    (SELECT Item_ID FROM Items WHERE ISBN = 9780077225957)
GROUP BY Condition

Good luck!

回复收藏 0 原文

~没有更多了~