GROUP BY 在 SQL 中使用参数 [英] GROUP BY using parameters in SQL

问题描述

我试图根据预定义的参数下拉列表以某种方式对报告进行分组.我希望能够根据部门或工作代码小计我的报告的总小时数或总工资.我已经创建了参数并且没有问题，我只是不确定是否可以使用这些参数来调用分组命令.以下是我想要的精神，但即使没有参数，GROUP BY 子句对我也不起作用.

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.

SELECT EmployeeID, LastName, FirstName, Department, JobCode, PayRate, SUM(Hours) as "Total Hours", SUM(Pay) as "Total Pay"
FROM Employees
GROUP BY @GroupBy

在 SQL 方面，我确实是个新手，因此非常感谢您的帮助.

I am truly a novice when it comes to SQL, so any help is very much appreciated.

谢谢.

推荐答案

这个要求对我来说不是 100% 清楚，但我想你会遇到这样的事情:

The requirement is not 100% clear to me, but I imagine your after something like this:

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;

尝试使用此设置:

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);

显然，您希望将 @groupBy 设置为 'dept' 或任何其他值.

Obviously, you want to set @groupBy to either 'dept' or any other value.

这篇关于GROUP BY 在 SQL 中使用参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！