选择仅按列分组，而不是聚合 [英] Select grouped by column only, not the aggregate

问题描述

在涉及聚合的 MySql 选择语句中，是否可以选择仅按列分组而不聚合?

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 should contain 1 column(s) 因为子查询同时选择了 id(我想要的)和总数(我没有选择).我可以以任何方式从子查询结果中排除 total 吗?

... 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 聚合问题中找不到确切答案.

推荐答案

你的查询应该是这样的:

Your query should be like this:

select idclient, business_name from client where idclient in
(
  select idclient 
  from payment 
  group by idclient
  having sum(amount) > 100
)

您需要将聚合函数放在有子句中，在子查询中您需要选择与 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屋！