如何为我的问题编写SQL查询？ [英] How to write sql query for my problem?

问题描述

我的访问数据库中有两个表，一个是product_details，另一个是bid_details。



i希望在gridview中显示所有product_details，各个产品的出价最高它存储在bid_details表中。我想只显示该产品的最高出价，因为bid_details表中的出价太多..



Product_id列是两个主题中的主键。



希望你能解决我的问题..

i have two tables in my access database one is product_details and another is bid_details.

i want to show all the product_details in a gridview with highest bid of respective products which is stored in a bid_details table. i want to show only highest bid of the product as there are so many bids in bid_details table..

Product_id column is a primary key in both talbes.

Hope you will solve my problem..

推荐答案

问题1：两者中的数据[product_id]主键相同表？那么为什么要打扰两张桌子呢？您在产品和出价之间宣布了1：1的关系。



实际上，出价表应使用product_details的product_id作为外键。现在，您可以通过单个产品引用多个出价。



一旦完成，有几种方法可以获得您想要的结果。



不知道你的桌面布局，这里是它的要点：



- 按bid_details表中的product_id分组并使用MAX （bid-field）

- 将结果加入到product_details表中。



我故意没有指定哪种类型的加入，因为我不知道你希望如何处理没有当前出价的产品（也许是处理ISNULL（出价，0）？）



更新：根据您的回复

类似于：

Problem 1: Same data [product_id] primary key in both tables? Then why bother with two tables? You declared a 1:1 relationship between products and bids.

In practice, the bid table should be using the product_id of product_details as a foreign key . Now you can have multiple bids referred to by a single product.

Once that is done, there are several methods to get your desired result.

Not knowing your table layout, here's the gist of it:

- Group by the product_id in the bid_details table and use MAX(bid-field)
- JOIN the result of this to the product_details table.

I deliberately didn't specify which type of join because I don't know how you wish to handle products that have no current bids (perhaps, handle with ISNULL(bid, 0) ?)

UPDATED: Per your reply
Something like:

SELECT A.item_name, MAX(B.bid) as Bid
FROM product_details as A INNER JOIN bid_details as B
ON A.product_id = B.product_id
GROUP BY A.item_name, B.product_id

但请记住，上面依赖于你设置了表格，正如我在第一部分所提到的那样这个答案。

But remember, the above relies upon you have tables set up as I alluded to in the first part of this answer.

这篇关于如何为我的问题编写SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！