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

最新推荐文章于 2024-07-19 16:12:43 发布

Curren.wong

最新推荐文章于 2024-07-19 16:12:43 发布

阅读量263

点赞数 1

分类专栏： SQL刷题笔记 # JOIN 文章标签： sql mysql 牛客网数据分析

本文链接：https://blog.csdn.net/qq_41729780/article/details/108025971

版权

SQL刷题笔记同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

JOIN

1 篇文章 0 订阅

订阅专栏

文章目录

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

emp_no	salary	from_date	to_date	dept_no
10002	72527	2001-08-02	9999-01-01	d001
10004	74057	2001-11-27	9999-01-01	d004
10005	94692	2001-09-09	9999-01-01	d003
10006	43311	2001-08-02	9999-01-01	d002
10010	94409	2001-11-23	9999-01-01	d006

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;