Question

刚学数据库，遇到一道关于数据库sql语句的题目，实在不会，请高手帮忙解答一下，谢谢！

EMPLOYEE(ESSN,Ename,Sex,Salary,Dnumber)，其中ESSN是主键， 存储员工的信息，包括社会保险号、姓名、工资、所属部门号
DEPARTMENT(Dnumber,Dname,MgrSSN) 其中Dnumber是主键，存储部门信息，包括部门编号、名称、经理社会保险号
PROJECT(Pnumber,Pname,Pcity) 其中Pnumber是主键，存储项目的信息，包括项目编号、名称、所在城市
WORKS_ON(Pnumber,ESSN,Wdate,Hours) 其中Pnumber、ESSN、Wdate是主键，存储某员工在某日为某项目工作的时间

请用SQL语句实现下面计算：
(1)求工资高于其所在部门平均工资的职工姓名
(2)求至少在三个不同项目中工作过的职工姓名
(3)建立视图DeptPro(Dnumber，Pnumber，Thours)，表示每个部门的所有职工在每个项目中的工作总时数。并利用该视图，求在项目'Middleware'中工作时数最多的部门名称。

Answer 1

1. select * from EMPLOYEE where Salary>(select avg(Salary) from EMPLOYEE group by Dnumber)
2. select * from EMPLOYEE where ESSN in (select ESSN from WORKS_ON group by Pnumber,ESSN having Count(1)>=3)
3. -- 每个部门的所有职工在每个项目中的工作总时数
Create View DeptPro
AS
select Pnumber, Dnumber, Dname, sum(Hours) Hours from (
select A.Pnumber, A.Hours, C.Dnumber, C.Dname
from WORKS_ON A
Inner join EMPLOYEE B on A.ESSN=B.ESSN
Inner join DEPARTMENT C on B.Dnumber=C.Dnumber
) Tbl group by Pnumber,Dnumber,Dname

--工作时数最多的部门名称。
select Max(Hours) Hours, Dnumber, Dname from DeptPro
where Pnumber in (select Pnumber from PROJECT where Pname='Middleware')
group by Dnumber, Dname

Answer 2

(1)
SELECT Ename, Dnumber, Salary FROM EMPLOYEE
JOIN
(SELECT Dnumber, avg(Salary) AS Asalary FROM EMPLOYEE
GROUP BY Dnumber) A
ON Dnumber = A.Dnumber
WHERE Salary > A.Asalary
ORDER BY Dnumber
（2）
SELECT Ename FROM EMPLOYEE
JOIN
(SELECT ESSN, COUNT(Pnumber) AS Pcount FROM WORKS_ON
GROUP BY ESSN
HAVING COUNT(Pnumber) > 3） A --未考虑同一个职工在不同时期参加同一个项目情况
ON ESSN = A.ESSN
(3)
CREATE VIEW DEPTPRO AS
(SELECT Dnumber, Pnumber, SUM(Hours) AS Thours FROM WORKS_ON
GROUP BY Dnumber, Pnumber); --每个部门的所有职工在每个项目中的工作总时数
SELECT A.Pname FROM DEPTPRO
JOIN DEPARTMENT A
ON Dnumber = A.Dnumber
JOIN PROJECT B
ON Pnumber = B.Pnumber
WHERE B.Pname = 'Middleware'
AND Thours = MAX(Thours);--在项目'Middleware'中工作时数最多的部门名称

追问

你好，首先谢谢你回答我的提问。
请问第(2)问如果考虑同一个职工在不同时期参加同一个项目情况时sql语句怎么写？
第(3) 问创建视图好像有点问题，WORKS_ON表中没有Dnumber这个属性，所以
GROUP BY Dnumber, Pnumber时这个属性是没有的，如果去掉这个Dnumber，则不是题目中所要求的每个部门的所有职工在每个项目中的工作总时数。

追答

你好。
第一个问题：
因为要求返回“至少在三个不同项目中工作过的职工姓名”，所以考虑同一个职工在不同时期参加同一个项目情况时，还需要去掉重复的项目。
SELECT Ename FROM EMPLOYEE
JOIN
(SELECT ESSN, COUNT(Pnumber) AS Pcount FROM
(SELECT DISTINCT ESSN, Pnumber FROM WORKS_ON) --去掉重复的员工—项目记录
GROUP BY ESSN
HAVING COUNT(Pnumber) > 3） A --考虑同一个职工在不同时期参加同一个项目情况
ON ESSN = A.ESSN

第二个问题：
是我修改时疏忽了。
CREATE VIEW DEPTPRO AS
(SELECT C.Dnumber, Pnumber, SUM(Hours) AS Thours FROM WORKS_ON
JOIN EMPLOYEE B ON ESSN=A.ESSN
JOIN DEPARTMENT B ON A.Dnumber=B.Dnumber
GROUP BY B.Dnumber, Pnumber); --每个部门的所有职工在每个项目中的工作总时数
yjvfhpssmarco创建视图的思路是正确的，语法好像有点问题。