仅选择按列分组,而不是聚合

发布于 2024-10-27 23:21:08 字数 583 浏览 6 评论 0原文

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

基本上我想根据基于聚合的条件在子查询中选择 ID,在本例中是向客户支付的总金额:

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 contains 1 columns因为子查询同时选择了 id(我想要的)和总计(我不想要的)。我可以以任何方式从子查询结果中排除 total 吗?

编辑:如果可能的话,我宁愿避免使用连接 - where 子句本身被传递到另一个现有函数。

如果这是一个骗局,我深表歉意 - 我确实进行了搜索,诚实。在大量的 SQL 聚合问题中我找不到确切的答案。

In a MySql select statement involving aggregation, is it possible to select just the grouped by column without the aggregate?

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
)

... 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?

Edit: if possible I would prefer to avoid using a join - the where clause is being passed onto another existing function on its own.

Apologies if this is a dupe - I did search, honest. I couldn't find an exact answer in the mass of SQL aggregate questions.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

橘和柠 2024-11-03 23:21:08

您的查询应该是这样的:

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

您需要将聚合函数放在having子句中,并且在子查询中您需要选择与where子句中相同的列数。

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
)

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.

摘星┃星的人 2024-11-03 23:21:08
WHERE idclient IN (...)

(...) 里面的东西是一个子查询。显然它应该只返回一列,因为 IN 子句只需要一列数据。

您可以通过以下方式省略总计列:

SELECT idclient
FROM payment
GROUP BY idclient
HAVING SUM(amount) > 100
WHERE idclient IN (...)

The stuff inside (...) is a subquery. Obviously it should only return one column, because you only need one column of data for the IN clause.

You can omit the total column by:

SELECT idclient
FROM payment
GROUP BY idclient
HAVING SUM(amount) > 100
七七 2024-11-03 23:21:08

您还可以尝试这个:

SELECT c.idclient, c.business_name
FROM payment p
  INNER JOIN client c ON c.idclient = p.idclient
GROUP BY c.idclient, c.business_name
HAVING SUM(p.amount) > 100

而且,因为 client.idclient 列看起来非常像 PK,您甚至可以从 GROUP BY 中省略 client.business_name。所以最终的查询将如下所示:

SELECT c.idclient, c.business_name
FROM payment p
  INNER JOIN client c ON c.idclient = p.idclient
GROUP BY c.idclient
HAVING SUM(p.amount) > 100

You could also try this one:

SELECT c.idclient, c.business_name
FROM payment p
  INNER JOIN client c ON c.idclient = p.idclient
GROUP BY c.idclient, c.business_name
HAVING SUM(p.amount) > 100

And, because the client.idclient column looks very much like a PK, you could probably even omit client.business_name from GROUP BY. So the final query would look like this:

SELECT c.idclient, c.business_name
FROM payment p
  INNER JOIN client c ON c.idclient = p.idclient
GROUP BY c.idclient
HAVING SUM(p.amount) > 100
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文