mysql - 从两个以上的表中检索数据

Question

我有五张 table 。

Employee(Fname,Lname,Empno(Primary),Bdate,Address,Salary,Dnumber(Foreign))
Department(Dname, Dnumber(Primary),Mgrno)
Location(Dnumber(Foreign),Dlocation)
Timesheet(Empno(foreign),Pnumber(Foreign),Hours_per_day)
Project(Pname,Pnumber(Primary),Location,Dnumber(Foreign))

如何检索:

一个。对于项目位置“ljk”，列出项目编号、控制部门和部门经理的Fname、Lname 和Address。

求“F”部门所有员工的工资总和，以及该部门工资的最大值、最小值和平均值。

对于每个部门，检索部门编号 (Dnumber)、员 worker 数和该部门的平均工资。

对于问题c。我只能检索有关部门、部门编号和员 worker 数的数据。我不确定如何在结果表中添加平均工资列。

对于另外两部分。我认为它需要加入 2 个以上的表。但是，我不确定这是否可能。

样本:员工:

+-------+----------+-------+------------+---------+--------+---------+
| Fname | Lname    | Empno | Bdate      | Address | Salary | Dnumber |
+-------+----------+-------+------------+---------+--------+---------+
| g     | a        |   755 | 1986-09-09 | how     |   6598 |     100 |
| d     | v        |   796 | 1969-12-03 | e       |      2 |     101 |
| r     | n        |   850 | 1979-12-01 | a       |     10 |     100 |
| n     | h        |   879 | 1979-12-02 | b       |      8 |     101 |
| k     | k        |   888 | 1979-12-03 | c       |      6 |     102 |
+-------+----------+-------+------------+---------+--------+---------+

部门:

+-----------+---------+-------+
| Dname     | Dnumber | Mgrno |
+-----------+---------+-------+
| F         |     100 |   850 |
| ll        |     101 |   879 |
| M         |     102 |   888 |
+-----------+---------+-------+

项目:

+----------+---------+----------+---------+
| Pname    | Pnumber | Location | Dnumber |
+----------+---------+----------+---------+
| a        |      79 | ljk      |     101 |
| a        |      89 | ljk      |     100 |
| mardf    |      90 | kjk      |     102 |
+----------+---------+----------+---------+

时间表:

+-------+---------+---------------+
| Empno | Pnumber | Hours_per_day |
+-------+---------+---------------+
|   850 |      89 |             6 |
|   888 |      90 |             6 |
|   879 |      79 |             9 |
+-------+---------+---------------+

地点:

+---------+-----------+
| Dnumber | Dlocation |
+---------+-----------+
|     100 | east      |
|     101 | west      |
|     102 | north     |
+---------+-----------+

我尝试过的:

(c)部分我试过了

SELECT Department.Dname, 
    Department.Dnumber, 
    COUNT(*) AS 'TOTAL EMPLOYEES' 
FROM Department 
INNER JOIN Employee 
    ON Department.Dnumber = Employee.Dnumber 
GROUP BY Department.Dnumber;

我不确定如何在此处添加平均工资列。

http://sqlfiddle.com/#!9/a512f1

CREATE TABLE Department(
    Dname varchar(10),
    Dnumber int(5),
    Mgrno int(5),
    PRIMARY KEY(Dnumber)
);

CREATE TABLE Employee(
    Fname varchar(20),
    Lname varchar(20),
    Empno int(5),
    Bdate date,
    Address varchar(10),
    Salary float(5),
    Dnumber int(5),
    PRIMARY KEY(Empno),
    FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber)
);


CREATE TABLE Location(
    Dnumber int(5),
    Dlocation varchar(10),
    PRIMARY KEY(Dlocation),
    FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber)
);

CREATE TABLE Project(
    Pname varchar(10),
    Pnumber int(5),
    Location varchar(10),
    Dnumber int(5),
    PRIMARY KEY(Pnumber),
    FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber)
);

CREATE TABLE Timesheet(
    Empno int(5),
    Pnumber int(5),
    Hours_per_day int(5),
    FOREIGN KEY(Empno) REFERENCES Employee(Empno),
    FOREIGN KEY(Pnumber) REFERENCES Project(Pnumber)
);

Answer 1

这应该让您开始解决问题 C:

SQL Fiddle

MySQL 5.6 架构设置:

CREATE TABLE Department(
    Dname varchar(10),
    Dnumber int(5),
    Mgrno int(5),
    PRIMARY KEY(Dnumber)
    );

CREATE TABLE Employee(
    Fname varchar(20),
    Lname varchar(20),
    Empno int(5),
    Bdate date,
    Address varchar(10),
    Salary float(5),
    Dnumber int(5),
    PRIMARY KEY(Empno),
    FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber)
    );


CREATE TABLE Location(
    Dnumber int(5),
    Dlocation varchar(10),
    PRIMARY KEY(Dlocation),
    FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber)
    );

CREATE TABLE Project(
    Pname varchar(10),
    Pnumber int(5),
    Location varchar(10),
    Dnumber int(5),
    PRIMARY KEY(Pnumber),
    FOREIGN KEY(Dnumber) REFERENCES Department(Dnumber)
    );

CREATE TABLE Timesheet(
    Empno int(5),
    Pnumber int(5),
    Hours_per_day int(5),
    FOREIGN KEY(Empno) REFERENCES Employee(Empno),
    FOREIGN KEY(Pnumber) REFERENCES Project(Pnumber)
    );

INSERT INTO `Department`
(`Dname`,`Dnumber`,`Mgrno`)
VALUES
('Dept1','100','850'),
('Dept2','101','879'),
('Dept3','102','888');

INSERT INTO `Project`
(`Pname`,`Pnumber`,`Location`,`Dnumber`)
VALUES
('adfdd','79','ljk','101'),
('ffff','89','jkj','100'),
('mardf','90','kjk','102');

INSERT INTO `Employee`
(`Fname`,`Lname`,`Empno`,`Bdate`,`Address`,`Salary`,`Dnumber`)
VALUES
('g','a','755','1986-09-09','how','6598','100'),
('d','v','796','1969-12-03','e','2','101'),
('r','n','850','1979-12-01','a','10','100'),
('n','h','879','1979-12-02','b','8','101'),
('k','k','888','1979-12-03','c','6','102');

查询 1:

SELECT 
  a.`Dname`, 
  a.`Dnumber`, 
  COUNT(DISTINCT b.`Empno`) AS `TOTAL EMPLOYEES`,
  AVG(b.`Salary`) as `Average Salary`,
  MIN(b.`Salary`) as `Minimum Salary`,
  MAX(b.`Salary`) as `Maximum Salary`
FROM `Department` a
LEFT JOIN `Employee` b
  ON a.`Dnumber` = b.`Dnumber`
GROUP BY a.Dnumber

Results :

| Dname | Dnumber | TOTAL EMPLOYEES | Average Salary | Minimum Salary | Maximum Salary |
|-------|---------|-----------------|----------------|----------------|----------------|
| Dept1 |     100 |               2 |           3304 |             10 |           6598 |
| Dept2 |     101 |               2 |              5 |              2 |              8 |
| Dept3 |     102 |               1 |              6 |              6 |              6 |