php - 使用 MySQL 示例员工数据库的 SQL 查询

Question

我在进行高级(对我而言)查询时遇到问题。

我正在尝试获取经理的名字、姓氏、职务、部门名称以及名字和姓氏。如果出于某种原因你需要知道的话，我正在 PHP 中使用这个 SQL。我限制为 10 个，因为数据库非常庞大。我只需要员工最新数据的条目。由于职位晋升等原因，他们有多个条目。

我包含了我认为您需要理解我的问题的所有内容。如果您能提供帮助，谢谢。

mysql>
SELECT DISTINCT employees.first_name, employees.last_name,
titles.title, departments.dept_name, z.first, z.last
FROM employees, dept_emp, departments, titles, 
(
  SELECT employees.first_name AS first, employees.last_name AS last
  FROM employees, dept_emp, dept_manager 
  WHERE 
        employees.emp_no = dept_emp.emp_no 
        AND dept_manager.emp_no = dept_emp.emp_no
) AS z
WHERE
   employees.emp_no = dept_emp.emp_no 
   AND dept_emp.dept_no = departments.dept_no
   AND titles.emp_no = employees.emp_no
LIMIT 10;

+------------+-----------+-----------------+-------------+-----------+--------------+
| first_name | last_name | title           | dept_name   | first     | last         |
+------------+-----------+-----------------+-------------+-----------+--------------+
| Georgi     | Facello   | Senior Engineer | Development | Margareta | Markovitch   |
| Georgi     | Facello   | Senior Engineer | Development | Vishwani  | Minakawa     |
| Georgi     | Facello   | Senior Engineer | Development | Ebru      | Alpin        |
| Georgi     | Facello   | Senior Engineer | Development | Isamu     | Legleitner   |
| Georgi     | Facello   | Senior Engineer | Development | Shirish   | Ossenbruggen |
| Georgi     | Facello   | Senior Engineer | Development | Karsten   | Sigstam      |
| Georgi     | Facello   | Senior Engineer | Development | Krassimir | Wegerle      |
| Georgi     | Facello   | Senior Engineer | Development | Rosine    | Cools        |
| Georgi     | Facello   | Senior Engineer | Development | Shem      | Kieras       |
| Georgi     | Facello   | Senior Engineer | Development | Oscar     | Ghazalie     |
+------------+-----------+-----------------+-------------+-----------+--------------+
10 rows in set (0.00 sec)

数据库是来自 mysql.com 的员工数据库: http://dev.mysql.com/doc/index-other.html

mysql> desc departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no   | char(4)     | NO   | PRI | NULL    |       |
| dept_name | varchar(40) | NO   | UNI | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.22 sec)

mysql> desc dept_emp;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int(11) | NO   | PRI | NULL    |       |
| dept_no   | char(4) | NO   | PRI | NULL    |       |
| from_date | date    | NO   |     | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.20 sec)

mysql> desc dept_manager;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| dept_no   | char(4) | NO   | PRI | NULL    |       |
| emp_no    | int(11) | NO   | PRI | NULL    |       |
| from_date | date    | NO   |     | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.21 sec)

mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.32 sec)

mysql> desc salaries;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no    | int(11) | NO   | PRI | NULL    |       |
| salary    | int(11) | NO   |     | NULL    |       |
| from_date | date    | NO   | PRI | NULL    |       |
| to_date   | date    | NO   |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.34 sec)

mysql> desc titles;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no    | int(11)     | NO   | PRI | NULL    |       |
| title     | varchar(50) | NO   | PRI | NULL    |       |
| from_date | date        | NO   | PRI | NULL    |       |
| to_date   | date        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.60 sec)

Answer 1

I am trying to get the first name, last name, title, department name, and first and last name of the manager

使用理论(但如果你有大表，它可能没有优化)，一步一步地进行。假设您有一个包含日期的 $date PHP 变量(如 date('Y-m-d 00:00:00') 的结果)，否则您可以使用 MySQL 的日期。

1/获取员工身份:

SELECT
    e.first_name,
    e.last_name
FROM
    employees AS e
WHERE
    1

2/添加他/她当前的头衔

SELECT
    e.first_name,
    e.last_name,
    t.title
FROM
    employees AS e,
    titles AS t
WHERE
    e.emp_no=t.emp_no AND t.from_date<='$date' AND t.to_date>='$date'

3/获取他/她当前的部门(来自dept_emp)并检索部门名称

SELECT
    e.first_name,
    e.last_name, t.title,
    d.dept_name
FROM
    employees AS e,
    titles AS t,
    dept_emp AS de,
    departments as d
WHERE
    e.emp_no=t.emp_no AND t.from_date<='$date' AND t.to_date>='$date'
AND
    e.emp_no=de.emp_no AND de.from_date<='$date' AND de.to_date>='$date'
AND
    d.dept_no=de.dept_no

4/获取部门经理(来自dept_manager)并检索他/她的姓名(来自employees)

SELECT
    e.first_name AS empFN,
    e.last_name AS empLN,
    t.title AS empT,
    d.dept_name AS dept,
    em.first_name AS manFN,
    em.last_name AS manLN
FROM
    employees AS e,
    titles AS t,
    dept_emp AS de,
    departments as d,
    dept_manager AS dm,
    employees AS em
WHERE
    e.emp_no=t.emp_no AND t.from_date<='$date' AND t.to_date>='$date'
AND
    e.emp_no=de.emp_no AND de.from_date<='$date' AND de.to_date>='$date'
AND
    d.dept_no=de.dept_no
AND
    em.emp_no=dm.emp_no AND dm.from_date<='$date' AND dm.to_date>='$date'
AND
    de.dept_no=dm.dept_no

您可以在其中附加一个LIMIT(可能还有一个ORDER BY，您不知道在出现限制的情况下会选择哪些结果)，和/或在 WHERE 中添加您想要了解其信息的员工的 emp_no。虽然没有经过测试，但主要是为了让您了解如何构建请求。

这是理论，处理这个请求可能和您写的请求一样繁重。由于您将 SQL 与 PHP 结合使用，因此最好仅检索您感兴趣的主键，然后发送简短的请求来检索您想要的信息。我的意思是类似的东西(使用假函数，但精神就在这里):

$dateString="from_date<='$date' AND to_date>='$date'";
$qe=query("SELECT emp_no, first_name, last_name, dept_no FROM employees, dept_emp WHERE dept_emp.emp_no=employees.emp_no LIMIT 10 ORDER BY emp_no DESC");
while($r=fetch($qe)) {
  $qt=fetch(query("SELECT title FROM titles WHERE emp_no=".$qe['emp_no']." AND ".$dateString));
  $qd=fetch(query("SELECT dept_name FROM departments WHERE dept_no=".$qe['dept_no']." AND ".$dateString));
  $qm=fetch(query("SELECT first_name, last_name FROM employees AS e, dept_manager AS dm WHERE dept_no=".$qe['dept_no']." AND e.emp_no=dm.emp_no AND ".$dateString));
  //echo/process here
}

希望这有帮助:)