仅显示SUM(价格)大于一定数量的那些行 [英] Display only those rows, whose SUM(price) is more than a certain number
本文介绍了仅显示SUM(价格)大于一定数量的那些行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有3个表:
- customers(name,id),
- items(名称,价格,ID)
- 购买(id,item_id,customers_id)
何时我运行:
select
customers.name,
SUM(items.price) as "price"
from items
INNER JOIN purchases
ON items.id = purchases.item_id
INNER JOIN customers
ON purchases.customer_id = customers.id
GROUP BY customers.name
ORDER BY "price"
这是我得到的结果:
"Anne Watson" "5.00"
"Craig Scott" "11.30"
"Michael Adam" "101.29"
"Jose Salvatierra" "899.00"
"Rolf Smith" "1174.50"
但是,我只想显示价格超过100的那些行(在这种情况下为3个最下面的行)。我该怎么办?
However, I would like to only show those rows, which have price over 100 (3 bottom ones in this case). How can I do that?
推荐答案
这是您使用 HAVING
子句。它类似于 WHERE
子句,但在 GROUP BY
之后应用。
This is where you use a HAVING
clause. It's similar to a WHERE
clause, but applied after the GROUP BY
.
HAVING SUM(items.price) > 100
这篇关于仅显示SUM(价格)大于一定数量的那些行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文