20160730_dbstudychugoku_LT

mysql_sample.sql

-- 1990年に雇った人の現在給与一覧を取得
SELECT
  departments.dept_name,
  employees.last_name AS manager_last_name,
  salaries.salary,
  titles.title,
  ninety_hire_man_employees.*
FROM (
    SELECT
      *
    FROM
      employees.employees
    WHERE
      hire_date between '1990-01-01'
      AND '1990-12-31'
      AND gender = 'M' ) AS ninety_hire_man_employees
  LEFT JOIN employees.salaries ON ninety_hire_man_employees.emp_no = salaries.emp_no
  AND salaries.to_date = '9999-01-01'
  LEFT JOIN employees.titles ON ninety_hire_man_employees.emp_no = titles.emp_no
  AND titles.to_date = '9999-01-01'
  LEFT JOIN employees.dept_emp ON ninety_hire_man_employees.emp_no = dept_emp.emp_no
  AND dept_emp.to_date = '9999-01-01'
  LEFT JOIN employees.departments ON dept_emp.dept_no = departments.dept_no
  LEFT JOIN employees.dept_manager ON dept_emp.dept_no = dept_manager.dept_no
  AND dept_manager.to_date = '9999-01-01'
  LEFT JOIN employees.employees ON dept_manager.emp_no = employees.emp_no
WHERE
  salaries.salary IS NOT NULL
ORDER BY
  departments.dept_name,
  salaries.salary DESC;

• Result:

+------------------+-------------------+--------+--------------+--------+------------+------------+--------------+--------+------------+
| dept_name        | manager_last_name | salary | title        | emp_no | birth_date | first_name | last_name    | gender | hire_date  |
+------------------+-------------------+--------+--------------+--------+------------+------------+--------------+--------+------------+
| Customer Service | Weedman           | 130956 | Senior Staff |  77129 | 1953-04-07 | Jaques     | Zaiane       | M      | 1990-04-03 |
| Customer Service | Weedman           | 128659 | Senior Staff |  12438 | 1953-11-24 | Yongmao    | Mondadori    | M      | 1990-12-19 |
| Customer Service | Weedman           | 116983 | Senior Staff |  45073 | 1961-08-19 | Xiadong    | Olivero      | M      | 1990-02-01 |
| Customer Service | Weedman           | 115357 | Senior Staff | 453362 | 1964-08-21 | Yucel      | Parascandalo | M      | 1990-06-27 |
| Customer Service | Weedman           | 114545 | Senior Staff | 265407 | 1953-05-26 | Richard    | Picht        | M      | 1990-09-20 |
| Customer Service | Weedman           | 113749 | Senior Staff | 263004 | 1956-06-09 | Goncalo    | Solares      | M      | 1990-01-22 |
| Customer Service | Weedman           | 112080 | Senior Staff | 100059 | 1962-06-04 | Kristinn   | Perfilyeva   | M      | 1990-03-15 |
| Customer Service | Weedman           | 111548 | Senior Staff |  86306 | 1955-08-11 | Persi      | Krzyzanowski | M      | 1990-12-31 |
| Customer Service | Weedman           | 110583 | Senior Staff | 476464 | 1958-11-20 | Qingxiang  | Pulkowski    | M      | 1990-01-14 |
| Customer Service | Weedman           | 110559 | Senior Staff | 241831 | 1956-04-24 | Jeanna     | Ranta        | M      | 1990-07-24 |
...
+------------------+-------------------+--------+--------------+--------+------------+------------+--------------+--------+------------+
12280 rows in set (1.68 sec)