具有max()的查询无法按预期工作

apeeds0o  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(288)

我在一个数据库中有四个表。表格如下所示:

Cashier(cashier_id*, cashier_name)
Category(category_id*, category_name)
Product(product_id*, product_name, price, category_id**)
Purchase(product_id**, cashier_id**, amount)

* primary key
**foreign key

我想选择购买次数最多的类别名称。这是我到目前为止所做的,但是我没有得到我想要的结果

SELECT x.category,x.amount
    FROM (SELECT category_name as category, SUM(amount) as amount FROM 
          Category c, Product pr, Purchase pu WHERE pr.product_id = 
          pu.product_id and c.category_id = pr.category_id GROUP BY 
          category_name) x
    GROUP BY x.category
    HAVING MAX(x.amount);
gz5pxeao

gz5pxeao1#

大概你想要这样的东西:

SELECT c.category_name as category, SUM(amount) as amount
FROM Category c JOIN
     Product pr
     ON c.category_id = pr.category_id JOIN
     Purchase pu 
     ON pr.product_id = pu.product_id
GROUP BY category_name
ORDER BY SUM(amount) DESC
LIMIT 1;

不要在句子中使用逗号 FROM 条款。始终使用适当的、明确的、标准的 JOIN 语法。

相关问题