postgresql 一个查询中有两个不同的 group by 子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5097149/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Two different group by clauses in one query?
提问by user631106
First time posting here, a newbie to SQl, and I'm not exactly sure how to word this but I'll try my best.
第一次在这里发帖，SQl 的新手，我不确定如何表达，但我会尽力而为。
I have a query:
我有一个疑问：
select report_month, employee_id, split_bonus,sum(salary) FROM empsal
where report_month IN('2010-12-01','2010-11-01','2010-07-01','2010-04-01','2010-09-01','2010-10-01','2010-08-01')
AND employee_id IN('100','101','102','103','104','105','106','107')
group by report_month, employee_id, split_bonus;
Now, to the result of this query, I want to add a new column split_bonus_cumulativethat is essentially equivalent to adding a sum(split_bonus)in the select clause but for this case, the group buy should only have report_month and employee_id.
现在，对于这个查询的结果，我想添加一个新列split_bonus_cumulative，它本质上相当于在select子句中添加一个sum(split_bonus)，但对于这种情况，团购应该只有report_month和employee_id。
Can anyone show me how to do this with a single query? Thanks in advance.
谁能告诉我如何通过一个查询来做到这一点？提前致谢。
回答by Alex Deem
Try:
尝试：
SELECT
report_month,
employee_id,
SUM(split_bonus),
SUM(salary)
FROM
empsal
WHERE
report_month IN('2010-12-01','2010-11-01','2010-07-01','2010-04-01','2010-09-01','2010-10-01','2010-08-01')
AND
employee_id IN('100','101','102','103','104','105','106','107')
GROUP BY
report_month,
employee_id;
回答by Denis de Bernardy
Assuming you're using Postgres, you might also find window functions useful:
假设您正在使用 Postgres，您可能还会发现窗口函数很有用：
http://www.postgresql.org/docs/9.0/static/tutorial-window.html
http://www.postgresql.org/docs/9.0/static/tutorial-window.html
Unless I'm mistaking, you want something that resembles the following:
除非我弄错了，否则您需要类似于以下内容的内容：
select report_month, employee_id, salary, split_bonus,
sum(salary) over w as sum_salary,
sum(split_bonus) over w as sum_bonus
from empsal
where ...
window w as (partition by employee_id);
CTEs are also convenient:
CTE 也很方便：
http://www.postgresql.org/docs/9.0/static/queries-with.html
http://www.postgresql.org/docs/9.0/static/queries-with.html
WITH
rows as (
SELECT foo.*
FROM foo
WHERE ...
),
report1 as (
SELECT aggregates
FROM rows
WHERE ...
),
report2 as (
SELECT aggregates
FROM rows
WHERE ...
)
SELECT *
FROM report1, report2, ...
