聚合 MySQL 函数是否总是返回单行? [英] Do aggregate MySQL functions always return a single row?

问题描述

如果这真的很基本，我很抱歉，但是:

I'm sorry if this is really basic, but:

我觉得在某些时候我没有这个问题，现在我有了，所以要么我以前做的事情完全不同，要么我的语法跳过了一步.

I feel at some point I didn't have this issue, and now I am, so either I was doing something totally different before or my syntax has skipped a step.

例如，我有一个查询，我需要返回包含某些数据的所有行以及另一列，该列具有这些列的总数.如果事情按我的预期工作，它看起来像:

I have, for example, a query that I need to return all rows with certain data along with another column that has the total of one of those columns. If things worked as I expected them, it would look like:

 SELECT
 order_id,
 cost,
 part_id,
 SUM(cost) AS total
 FROM orders 
 WHERE order_date BETWEEN xxx AND yyy

我会得到我的订单的所有行，并将总数添加到每个订单的末尾.我知道每次的总数都是一样的，但这是意料之中的.现在要让它工作我正在使用:

And I would get all the rows with my orders, with the total tacked on to the end of each one. I know the total would be the same each time, but that's expected. Right now to get that to work I'm using:

 SELECT
 order_id,
 cost,
 part_id,
 (SELECT SUM(cost)
 FROM orders
 WHERE order_date BETWEEN xxx AND yyy) AS total
 FROM orders 
 WHERE order_date BETWEEN xxx AND yyy

基本上两次运行相同的查询，一次用于总数，一次用于其他数据.但是，如果我想要 SUM 并且我不知道平均成本，那么我会执行相同的查询 3 次，这似乎真的是错误的，这就是为什么我认为我正在做一些非常基本的错误.

Essentially running the same query twice, once for the total, once for the other data. But if I wanted, say, the SUM and, I dunno, the average cost, I'd then be doing the same query 3 times, and that seems really wrong, which is why I'm thinking I'm making some really basic mistake.

非常感谢任何帮助.

推荐答案

需要使用GROUP BY 这样就可以得到你想要的结果:

You need to use GROUP BY as such to get your desired result:

SELECT
order_id,
part_id,
SUM(cost) AS total
FROM orders 
WHERE order_date BETWEEN xxx AND yyy
GROUP BY order_id, part_id

这将对您的结果进行分组.请注意，由于我假设 order_id 和 part_id 是复合 PK，因此上面的 SUM(cost) 可能是 = cost(因为你是通过两个字段的组合进行分组，保证是唯一的.下面的相关子查询将克服这个限制).

This will group your results. Note that since I assume that order_id and part_id is a compound PK, SUM(cost) in the above will probably be = cost (since you a grouping by a combination of two fields which is guarantied to be unique. The correlated subquery below will overcome this limitation).

需要在 GROUP BY 行中指定提取的任何非聚合行.

Any non-aggregate rows fetched needs to be specified in the GROUP BY row.

有关更多信息，您可以在此处阅读有关 GROUP BY 的教程:

For more information, you can read a tutorial about GROUP BY here:

MySQL 教程 - 分组方式

<小时>

如果您想将列同时用作聚合和非聚合，或者如果您需要取消对组的隔离，则需要使用子查询:

---

If you want to use a column as both aggregate and non-aggregate, or if you need to desegregate your groups, you will need to use a subquery as such:

SELECT
or1.order_id,
or1.cost,
or1.part_id,
(
  SELECT SUM(cost)
  FROM orders or2
  WHERE or1.order_id = or2.order_id
  GROUP BY or2.order_id
) AS total
FROM orders or1
WHERE or1.order_date BETWEEN xxx AND yyy

这篇关于聚合 MySQL 函数是否总是返回单行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！