聚合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 （因为你通过组合的两个字段组合保证是唯一的。

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教程 - 分组由

---

EDIT：列作为聚合和非聚合，或者如果您需要解除组合，则需要使用子查询：

---

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屋！