的Oracle SQL跨多个表

Question

这里查询平均有关系：

CREATE TABLE employee (
    name varchar2(15) not null, 
    ssn  char(9), 
    sex  char, 
    salary number(10,2), 
    dno  number(4), 
    primary key (ssn), 
    foreign key (dno) references department(dnumber) 
); 

CREATE TABLE department (
    dname  varchar2(15) not null, 
    dnumber  number(4), 
    primary key (dnumber), 
    unique (dname), 
); 

Q1：对于每个部门，其员工平均薪水超过$ 30,000，检索部门名称和员工的该部门工作的人数。第二季度：假设我们希望每个部门的男性员工人数而不是所有员工（如Q1）来计算部门平均数和员工人数。我们可以在SQL中指定这个查询吗？为什么或者为什么不。

谢谢！

Answer 1

你的第一个查询可能看起来像

SELECT d.dname, 
     COUNT(*) total_employees 
    FROM department d LEFT JOIN 
     employee e ON d.dnumber = e.dno 
GROUP BY d.dname 
HAVING AVG(e.salary) > 30000; 

，第二个

SELECT d.dname, 
     COUNT(*) male_employees 
    FROM department d LEFT JOIN 
     employee e ON d.dnumber = e.dno 
WHERE e.sex = 'M' 
GROUP BY d.dname 
HAVING AVG(e.salary) > 30000; 

SQLFiddle

Answer 2

如果你想获得所有员工的数量和男性的数量员工在一个SQL中，你可以试试这个。

SELECT B.DNAME, 
COUNT(*) AS TOTAL_EMPLOYEES, 
COUNT(CASE WHEN A.SEX='M' THEN A.SEX END) AS MALE_EMPLOYEES, 
AVG(A.SAL) AS ALL_AVG, 
AVG(CASE WHEN A.SEX='M' THEN A.SAL END) AS MALE_AVG 
FROM EMPLOYEE A JOIN DEPARTMENT B 
ON A.DNO=B.DNUMBER 
GROUP BY B.DNAME 

Answer 3

SELECT dname, COUNT(*) AS Num_of_Employees  
FROM employee, department  
WHERE dno = dnumber AND EXISTS (
     SELECT AVG(Salary)  
     FROM employee  
     GROUP BY dno  
     HAVING AVG(Salary)>30000)  
GROUP BY dname;