我需要帮助创建一个查询sql

问题描述：

我有两个表。一个持有员工，一个持有项目。员工表有一个主键，项目有一个外键来标识哪个员工在哪个项目上工作。我需要编写一个查询来返回员工的名字和姓氏，但仅限于那些处理两个或更多项目的员工。这是我到目前为止有：我需要帮助创建一个查询sql

create table employee 
(
employeeId int not null identity(1,1) primary key, 
firstName varChar(25) not null, 
lastName varChar(25) not null, 
gender varChar(1) not null, 
dob date not null, 
ssn varChar(9) not null, 
) 

insert into employee 
values 
    ('Peter','Smith','m','1979-08-25','123112233'), 
    ('Juan','Doe','m','1985-05-22','435678907'), 
    ('Rafael','Perez','m','1979-03-22','754984568' 

create table projects 
(
projectId int identity(1,1) primary key not null, 
description varChar(100) not null, 
status varChar(10) not null, 
startDate date not null, 
projectedEndDate date not null, 
manager int foreign key references employee(employeeId)

），返回

insert into projects 
values ('this project will improve the power on certain devices','active','2014-09-12','2015-0101','1'), 
    ('this project will improve the user interface','active','2014-09-12','2015-01-01','1'), 
    ('this project will improve the load time','active','2014-09-12','2015-01-01','2'), 
    ('this project will implement stronger security','active','2014-09-12','2015-01-01','2') 

    select firstname +' '+ lastname as fullname 
    from projects 
    inner join employee 
    on employee.employeeid = projects.manager

Peter Smith 
Peter Smith 
Juan Doe 
Juan Doe 
Rafael Perez

我读过使用计数，但一直没能实现与加盟伯爵已经停留了一段时间。

答

你想要一个group by和having条款：

select firstname +' '+ lastname as fullname 
from projects inner join 
    employee 
    on employee.employeeid = projects.manager 
group by firstname +' '+ lastname 
having count(*) >= 2;

答

您可以随时换你的SQL：

SELECT fullname FROM (
    select firstname +' '+ lastname as fullname 
    from projects 
    inner join employee 
    on employee.employeeid = projects.manager) t 
GROUP BY fullname 
HAVING COUNT(distinct projectId) > 1

我需要帮助创建一个查询sql

相关推荐