博客

1. 题目描述

1.1. Time Limit

C/C++ 1秒，其他语言2秒

1.2. Memory Limit

C/C++ 32M，其他语言64M

1.3. Problem Description

查找各个部门当前

(dept_manager.to_date='9999-01-01')

领导当前

(salaries.to_date='9999-01-01')

薪水详情以及其对应部门编号

dept_no

(注:请以

salaries

表为主表进行查询，输出结果以

salaries.emp_no

升序排序，并且请注意输出结果里面

dept_no

列是最后一列)

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL, -- '员工编号',
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, --  '员工编号'
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

1.4. Output

1.5. Source

牛客网 SQL实战 3 查找当前薪水详情

2. 题解

可以使用

JOIN

查询，也可以在

FROM

后面接两个表名实现

INNER JOIN

。

根据

Mysql 8.0 官方文档

，

FROM

后跟的

table_references

展开为

escaped_table_reference [, escaped_table_reference] ...

是可以通过逗号分隔并列的，同样实现的是

INNER JOIN

。

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference: {
    table_reference
  | { OJ table_reference }
}

table_reference: {
    table_factor
  | joined_table
}

table_factor: {
    tbl_name [PARTITION (partition_names)]
        [[AS] alias] [index_hint_list]
  | [LATERAL] table_subquery [AS] alias [(col_list)]
  | ( table_references )
}

3. 代码

先连接两个表，对两张表的

to_data

字段都需要进行筛选，最后按照

emp_no

升序排列。

SELECT t1.*, `dept_no`
FROM `salaries` as t1
JOIN `dept_manager` as t2
ON t1.emp_no = t2.emp_no
WHERE t1.to_date = '9999-01-01'
AND t2.to_date = '9999-01-01'
ORDER BY t1.emp_no;

在

FROM

后使用逗号分隔，连接两个表，实现

INNER JOIN

，

SQLite

中需要在后面接

ON

。

SELECT t1.*, `dept_no`
FROM `salaries` as t1, `dept_manager` as t2
ON t1.emp_no = t2.emp_no
WHERE t1.to_date = '9999-01-01'
AND t2.to_date = '9999-01-01'
ORDER BY t1.emp_no;


Mysql

中需要加

WHERE

，不能加

ON

，下面的语句在

SQLite

中也可以运行。

SELECT t1.*, `dept_no`
FROM `salaries` as t1, `dept_manager` as t2
WHERE t1.emp_no = t2.emp_no
AND t1.to_date = '9999-01-01'
AND t2.to_date = '9999-01-01'
ORDER BY t1.emp_no;

联系邮箱

：curren_wong@163.com

CSDN

：

https://me.csdn.net/qq_41729780

知乎

：

https://zhuanlan.zhihu.com/c_1225417532351741952

公众号

：

复杂网络与机器学习

欢迎关注/转载，有问题欢迎通过邮箱交流。