聚合 MySQL 函数是否总是返回单行?
如果这真的很基本，我很抱歉，但是:
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
相关文章