GROUP BY 在 SQL 中使用参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8857090/
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
GROUP BY using parameters in SQL
提问by Jeff Trujillo
I am trying to somehow group a report based on a drop-down list of parameters that is pre-defined. I want to be able to subtotal the Total Hours or Total Pay of my report based on Department or JobCode. I have created the parameters and have no problem with that, I just am not sure if it's possible to use those parameters to call out a grouping command. Below is the spirit of what I am wanting, but the GROUP BY clause doesn't work for me even without a parameter.
我试图根据预定义的参数下拉列表以某种方式对报告进行分组。我希望能够根据部门或工作代码对我的报告的总小时数或总薪酬进行小计。我已经创建了参数并且没有问题，我只是不确定是否可以使用这些参数来调用分组命令。下面是我想要的精神，但即使没有参数，GROUP BY 子句对我也不起作用。
SELECT EmployeeID, LastName, FirstName, Department, JobCode, PayRate, SUM(Hours) as "Total Hours", SUM(Pay) as "Total Pay"
FROM Employees
GROUP BY @GroupBy
I am truly a novice when it comes to SQL, so any help is very much appreciated.
在 SQL 方面，我确实是一个新手，因此非常感谢任何帮助。
Thank You.
谢谢你。
回答by René Nyffenegger
The requirement is not 100% clear to me, but I imagine your after something like this:
这个要求对我来说不是 100% 清楚，但我想象你的事情是这样的：
select
case when @groupBy = 'dept' then
department else
jobCode end dept_jobCode,
sum(hours)
from employees
group by
case when @groupBy = 'dept' then
department else
jobCode end;
To be tried with this setup:
要尝试使用此设置：
create table employees (
lastName varchar(20),
department varchar(20),
jobCode varchar(20),
hours number
);
insert into employees values ('Miller', 'Dept 1', 'A', 10);
insert into employees values ('Doe' , 'Dept 1', 'A', 7);
insert into employees values ('Baker' , 'Dept 1', 'B', 4);
insert into employees values ('Sand' , 'Dept 2', 'B', 6);
insert into employees values ('Stark' , 'Dept 2', 'B', 9);
insert into employees values ('Gild' , 'Dept 2', 'A', 9);
Obviously, you want to set @groupByto either 'dept'or any other value.
显然，您希望设置@groupBy为任一'dept'值或任何其他值。
回答by JNK
I think you are fundamentally misunderstanding how GROUP BYworks.
我认为您从根本上误解了GROUP BY工作原理。
GROUPINGis a way to aggregate many rows together.
GROUPING是一种将多行聚合在一起的方法。
If you return any fields not in the GROUP BY, you need to decide what to do with them. You can't NOTdo anything with them because you could have multiple values per group. They must be either excluded or aggregated.
如果您返回任何不在 中的字段GROUP BY，您需要决定如何处理它们。你无法不与他们做任何事情，因为你可以有每组多个值。它们必须被排除或汇总。
To aggregate them, you need to decide what function to use (MAX, MIN, SUM, AVG, etc).
要聚合它们，你需要决定哪些功能，使用（MAX，MIN，SUM，AVG，等）。
If you want to show many rows, say one per employee, but want to include some information about totals for that employee's department, you need to use a subquery:
如果您想显示多行，比如每个员工一个，但想要包含有关该员工部门总数的一些信息，您需要使用子查询：
SELECT employeeid, <other unaggregated fields>
FROM MyTable t
INNER JOIN (SELECT DepartmentID, SUM(Totalhours) as TotHours...etc
FROM SomeOtherTable
GROUP BY DepartmentID) as Sub
ON Sub.DepartmentID = t.departmentID
There may be a way to do this dynamically but that is a pretty poor idea.
可能有一种方法可以动态执行此操作，但这是一个非常糟糕的主意。
回答by JSuar
Any column you are selecting that are not used by one of the aggregate function (SUM, MIN, etc) needs to be listed in the GROUP BYclause.
您选择的任何一个聚合函数（SUM、MIN 等）未使用的列都需要在GROUP BY子句中列出。
For example,
例如，
SELECT EmployeeID, LastName, FirstName, SUM(Hours) as "Total Hours" FROM Employees GROUP BY EmployeeID, LastName, FirstName
Good examples here: http://www.w3schools.com/sql/sql_groupby.asp
回答by GSerg
Group byis simple really.
Group by真的很简单。
You have to list in group byevery field that is included in the select statement and not fed to an aggregate function.
您必须列出group by包含在 select 语句中且未提供给聚合函数的每个字段。
Which is why you can't have a variable group bywith a fixed list of columns in select. (Well, you can in mysql, but it effectively applies virtual any()aggregate to them.)
这就是为什么你不能group by在select. （好吧，您可以在 mysql 中，但它有效地将虚拟any()聚合应用于它们。）
