选择仅按列分组,而不是合计 [英] Select grouped by column only, not the aggregate
问题描述
在涉及聚合的MySql select语句中,是否可以仅选择按列分组而没有聚合的分组?
In a MySql select statement involving aggregation, is it possible to select just the grouped by column without the aggregate?
基本上,我想根据基于总计的条件在子查询中选择ID,在这种情况下,向客户的总付款额:
Basically I want to select IDs in subquery according to a criteria based on an aggregate, in this case the total payments to a client:
select idclient, business_name from client where idclient in
(
select idclient, sum(amount) as total
from payment
group by idclient
having total > 100
)
...但这失败并显示错误 Operand应该包含1列
,因为子查询同时选择了ID(我想要的)和总计(我不需要的)。是否可以通过任何方式从子查询结果中排除总数
?
... but this fails with error Operand should contain 1 column(s)
because the subquery selects both the id (which I want) and the total (which I don't). Can I exclude total
from the subquery result in any way?
编辑:如果可能,我宁愿避免使用一个联接-where子句将自己传递给另一个现有函数。
if possible I would prefer to avoid using a join - the where clause is being passed onto another existing function on its own.
很抱歉,这很抱歉-我确实做了搜索。我在大量的SQL汇总问题中找不到确切的答案。
推荐答案
您的查询应该像此:
select idclient, business_name from client where idclient in
(
select idclient
from payment
group by idclient
having sum(amount) > 100
)
您需要将聚合函数放在having子句中,并且在子查询中,您需要选择与where子句中相同的列数。
You need to put aggregate function in having clause and in sub query you need to select # of columns same as in your where clause.
这篇关于选择仅按列分组,而不是合计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!