目錄

8.1 WITH AS 的含義

8.2 WITH AS語法的基本結構如下：

8.3 練習題1

8.4 牛客練習題

---

8.1 WITH AS 的含義

WITH AS 語法是MySQL中的一種臨時結果集，它可以在SELECT、INSERT、UPDATE或DELETE語句中使用。通過使用WITH AS語句，可以將一個查詢的結果存儲在一個臨時表中，然后在后續的查詢中引用這個臨時表。這樣可以簡化復雜的查詢，提高代碼的可讀性和可維護性。

8.2 WITH AS語法的基本結構如下：

WITH temporary_table_name AS (SELECT column1, column2, ...FROM table_nameWHERE condition
)
SELECT * FROM temporary_table_name;

其中，temporary_table_name是臨時表的名稱，table_name是要查詢的表名，condition是篩選條件。

8.3 練習題1

數據庫

drop table if exists  `dept_emp` ;
drop table if exists  `salaries` ;
CREATE TABLE `dept_emp` (`emp_no` int(11) NOT NULL,`dept_no` char(4) NOT NULL,`from_date` date NOT NULL,`to_date` date NOT NULL,PRIMARY KEY (`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`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','9999-01-01');INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
INSERT INTO salaries VALUES(10009,95409,'2002-02-14','9999-01-01');

有一個員工表dept_emp簡況如下:

有一個薪水表salaries簡況如下:

問題1：獲取每個部門中當前員工薪水最高的相關信息，給出dept_no, emp_no以及其對應的salary，按照部門編號dept_no升序排列，以上例子輸出如下:

with temp as (select dept_no,d.emp_no,salaryfrom dept_emp as dinner join salaries as son d.emp_no=s.emp_no
)
select temp.dept_no,emp_no,maxSalary
from temp
inner join(
select dept_no, max(salary) as maxSalary
from temp
group by dept_no) as t2
on temp.dept_no=t2.dept_no and t2.maxSalary=salary
order by temp.dept_no;

8.4 牛客練習題

SQL206 獲取每個部門中當前員工薪水的最高的相關信息