sql - 将查询转换为 SQL Server 语法
问题描述
我一直在从 Chegg 那里得到帮助,但大多数给我的答案都对我不起作用。我的第一个任务是编写一个查询,列出分配给最多项目的员工。
一种回应是:
select emp_name
from employee
where dept_ID in ( select dept_ID from project group by dept_ID order by count(proj_ID) desc);
我得到的错误:
消息 1033,级别 15,状态 1,第 3 行 ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效,除非还指定了 TOP、OFFSET 或 FOR XML。
第二个回应是
select w1.emp_id
from work_period w1
group by w1.emp_id
having count(w1.proj_id) = (select max(count(w2.proj_id)) from work_period w2 group by w2.emp_id);
我得到的错误是:
消息 130,级别 15,状态 1,第 8 行无法对包含聚合或子查询的表达式执行聚合函数。
第三个回应是
Select emp_ID,emp_Name
from Employee inner join Project on Employee.Dept_ID = Project.Dept_ID
group by emp_ID,emp_Name
having count(Proj_ID) = max(count(Proj_ID));
错误信息:
消息 130,级别 15,状态 1,第 13 行无法对包含聚合或子查询的表达式执行聚合函数。
非常感谢任何有关如何解决这些查询之一的帮助
如果有帮助,这是创建我的数据库的脚本
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);
解决方案
您可以使用subquery
获取项目数量最多的员工,max()
以及count()
聚合功能。
select * from employee where dept_id in (
select max(ct) from
(select count(1) ct, t1.dept_id from department t1
inner join project t2 on t2.dept_id = t1.dept_id
group by t1.dept_id) t3 )
推荐阅读
- react-native - React Native 将选定的 asyncStorage 值传递给 Popup onPress
- c - sprintf 反转一个 int 并存储为 char
- php - 如何在 PHP 中重命名上传的文件
- javascript - 检查字符串是否包含 JavaScript 代码或普通字符串?
- c# - Vistual Studio 2017 C# 诊断工具 关闭括号的函数占用大量时间意味着什么?
- unity3d - unity游戏无法玩
- query-string - AWS Cognito 托管的 UI 在 URL 中返回 id_token
- python - Pandas:检查列中是否存在值,创建一个新列,如果不存在则添加 0
- spring-boot - 在构造函数中使用 @Value 时,Junit 5 中的 @MOCK 返回 NULL
- python - Python 和 PiCamera 颜色检测布尔问题