带有父参数的 SQL 嵌套查询

发布于 2024-11-08 16:59:14 字数 434 浏览 0 评论 0原文

我需要编写该场景的查询，但我失去了编写它的能力。

假设有两个表 Items 和 Bid。通过某种过滤器选择项目

SELECT i.* FROM Items i WHERE i.Id = 2

现在有一个投标表，其中包含“ItemId”列，用于将项目链接到投标。现在我想要所有项目的数据包含 HighestBid、LowestBid 和 TotalBids，我正在尝试此操作，但它不起作用。

SELECT i.*, hal.* 
FROM Items i, (SELECT MAX(b.OfferAmount), MIN(b.OfferAmount), COUNT(b.*) FROM Bids b WHERE b.ItemId = i.Id) As hal
WHERE i.Id = 2

这有什么问题吗？

原文

I need to write a query of the scenario but I am lost writing it.

Assume there are two tables Items and Bids. The items are being selected via some sort of filter

SELECT i.* FROM Items i WHERE i.Id = 2

Now there is a Bids table having "ItemId" column to link Items to Bids. Now I want all items' data with HighestBid, LowestBid and TotalBids and I am trying this but it's not working.

SELECT i.*, hal.* 
FROM Items i, (SELECT MAX(b.OfferAmount), MIN(b.OfferAmount), COUNT(b.*) FROM Bids b WHERE b.ItemId = i.Id) As hal
WHERE i.Id = 2

Is there something wrong with this?

分享到QQ

分享到微博

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

发布评论

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

暮年慕年 2024-11-15 16:59:14

试试这个

SELECT i.*, 
       hal.* 
FROM   items i 
       INNER JOIN (SELECT MAX(b.offeramount), 
                          MIN(b.offeramount), 
                          b.itemid, 
                          COUNT(b.*) 
                   FROM   bids b 
                   GROUP  BY itemid) AS hal 
         ON i.Id= hal.itemid 
WHERE  i.id = 2

Try this

SELECT i.*, 
       hal.* 
FROM   items i 
       INNER JOIN (SELECT MAX(b.offeramount), 
                          MIN(b.offeramount), 
                          b.itemid, 
                          COUNT(b.*) 
                   FROM   bids b 
                   GROUP  BY itemid) AS hal 
         ON i.Id= hal.itemid 
WHERE  i.id = 2

回复收藏 0 原文

~没有更多了~