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.
这篇关于选择仅按列分组,而不是聚合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:选择仅按列分组,而不是聚合
基础教程推荐
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01