带有 group by 和 having 子句的 Mysql 查询

Question

我正在构建查询以获取同一产品的最高价格。我有表

-----------------------------------------
|   prod_id  |   price   |    user_id   |
-----------------------------------------
|     4      |   25      |       1      |
|     4      |   38      |       2      |
|     4      |   41      |       3      |
|     7      |   100     |       1      |
|     7      |   95      |       2      |
-----------------------------------------

我正在尝试获得以下输出:

-----------------------------------------
|     4      |   41      |       3      |
-----------------------------------------
|     7      |   100     |       1      |
-----------------------------------------

我构造了以下不正确的查询。

select * from user_bid group by prod_id having max(price);

谁能指导我获得所需结果的查询。

Answer 1

SELECT *
FROM user_bid 
WHERE (prodid, price) IN
(
    SELECT prodid, MAX(price)
    FROM user_bid 
    GROUP BY prod_id
)