将查询转换为 SQL Server 语法

Question

我一直在从Chegg那里得到帮助，但给我的大多数答案都对我不起作用。我的第一个任务是编写一个查询，其中列出了工作时间最多的项目。

一个回答是：

Select project.Proj_ID , proj_Name from Project inner join work_period on Project.Proj_ID = work_period.Proj_Id group by project.Proj_ID , proj_Name having count(weekly_work_hrs) = max(count(weekly_work_hrs));

我得到的错误：

Msg 130，级别 15，状态 1，第 1 行 无法对包含聚合或子查询的表达式执行聚合函数。

第二个响应是

CREATE VIEW project_with_most_hours AS
select w1.proj_id
from work_period w1   
group by w1.proj_id   
having sum(w1.weekly_work_hrs) = (select max(sum(w2.weekly_work_hrs)) from work_period w2 group by w2.proj_id);

我得到的错误是：

Msg 130，级别 15，状态 1，过程 project_with_most_hours，第 5 行 [批处理起始行 0] 无法对包含聚合或子查询的表达式执行聚合函数。

我寻找的最终结果是控制台仅显示 1 个Proj_ID或 proj_name 个工作时间最多的时间，这些小时数与该项目中工作的员工的所有工作时间相加。

非常感谢有关如何解决这些查询中的任何一个的任何帮助

这是创建我的数据库的脚本，如果它有帮助

create table department
(
dept_ID int not null ,
dept_name char(50) NOT NULL,
manager_ID int not null,
manager_start_date date not null,
constraint Dept_PK primary key (dept_ID),
constraint D_Name_AK unique (dept_name)
);
insert into department values(1,'abc',1,'2019-01-08');
insert into department values(2,'abc2',2,'2019-01-08');
insert into department values(3,'abc3',2,'2019-01-08');
insert into department values(4,'abc4',2,'2019-01-08');
/*project table done*/
create table project
(
proj_ID int not null,
proj_name varchar(20) not null,
dept_ID int not null,
proj_location varchar(20) not null,
constraint Proj_ID_PK primary key (proj_ID),
constraint Proj_Dep_FK foreign key (dept_ID) references department(dept_ID)
);
insert into project values ( 1,'project1',1,'india');
insert into project values ( 2,'project2',2,'US');
/*employee table done*/
create table employee
(
emp_ID int NOT NULL ,
emp_name char(50) not null,
emp_ssn char(11) not null,
emp_address char(50) not null,
salary decimal(10,2) not null,
sex char(1) not null,
date_of_birth date not null,
dept_ID int not null,
supervisor_ID int null,
constraint emp_PK primary key(emp_ID),
constraint emp_Name_AK unique (emp_name),
constraint emp_SSN_AK unique (emp_ssn),
constraint sup_FK foreign key(supervisor_ID) references employee(emp_ID),
constraint empDep_FK foreign key(dept_ID) references department(dept_ID)
);
insert into employee values( 1,'jagmeet', 'ssn','patel nagar',300,'M','1997-07-01',1,1);
insert into employee values( 2,'harpreet', 'ssn1','patel nagar2',300,'F','1997-07-01',1,2);
/*Department location table done*/
create table dept_location
(
dept_ID int not null,
location char(50) not null,
constraint dept_location_PK primary key(dept_ID, location),
constraint dept_FK foreign key (dept_ID) references department(dept_ID)
);
insert into dept_location values(1,'loc1');
insert into dept_location values(2,'loc2');
/*dependent table done*/
create table dependent
(
dependent_ID int not null ,
emp_ID int NOT NULL,
dependent_name varchar(20) NOT NULL,
dependent_sex char(1) NOT NULL,
dependent_DOB date not null,
dep_relation varchar(10) not null,
constraint dep_ID_PK primary key (dependent_ID),
constraint deb_emp_ID_FK foreign key (emp_ID) references employee(emp_ID)
);
insert into dependent values (1,2,'deptname','M','2018-01-09','rel1');
insert into dependent values (2,1,'deptname2','F','2018-01-09','rel2');
/*work period table done*/
create table work_period
(
emp_ID int NOT NULL,
proj_ID int not null,
pay_period date not null,
weekly_work_hrs int not null,
constraint pay_period_PK primary key (pay_period),
constraint WP_empID_FK foreign key (emp_ID) references employee(emp_ID),
constraint WP_projID_FK foreign key (proj_ID) references project(proj_ID)
);
insert into work_period values(1,1,'2012-09-01',7);
insert into work_period values(2,2,'2014-09-01',8);

Answer 1

使用CTE、查询以仅显示与每周工作时间匹配max数据

;
WITH cte
AS (SELECT
project.Proj_ID,
proj_Name,
SUM(weekly_work_hrs) sumofhrs
FROM Project
INNER JOIN work_period
ON Project.Proj_ID = work_period.Proj_Id
GROUP BY project.Proj_ID,
proj_Name)
SELECT
*
FROM cte
WHERE sumofhrs = (SELECT
MAX(sumofhrs)
FROM cte)

Answer 2

您可以查询、聚合并仅获取 TOP 1

SELECT TOP 1 WITH TIES
P.Proj_ID,
P.proj_Name,
SUM( WP.weekly_work_hrs) sumofhrs
FROM 
Project P
JOIN work_period WP
ON P.Proj_ID = WP.Proj_Id
GROUP BY 
P.Proj_ID,
P.proj_Name
order by
SUM( WP.weekly_work_hrs) DESC

Answer 3

您可以按降序ORDER BY小时数的总和，并且仅使用TOP 1来选择顶部结果。如果要在平局的情况下显示所有顶级项目，请使用WITH TIES选项TOP。否则，省略WITH TIES以(随机(选择一个，以防平局。您可能还想LEFT JOIN而不是INNER JOIN这样，如果您只有没有人处理过的项目，但它们无论如何都会输出 - 零小时在技术上也是有效的最大值。

SELECT TOP 1
WITH TIES
p.proj_id,
p.proj_name
FROM project p
LEFT JOIN work_period wp
ON wp.proj_id = p.proj_id
GROUP BY p.proj_id,
p.proj_name
ORDER BY sum(coalesce(wp.weekly_work_hrs, 0)) DESC;