秋霞网在线伦日伦,五月天婷婷伊人,神马午夜三级片,国产内射久久,苏畅md一区二区三区在线观看,亚洲av在线免费看,一区二区三区四区在线播放,亚洲精品成人无码熟妇在线

點(diǎn)擊上方藍(lán)字關(guān)注「網(wǎng)優(yōu)小兵玩Python」

◆?◆?◆

創(chuàng)建Employee 表，包含所有員工信息，每個員工有其對應(yīng)的 Id, salary 和 department Id。

-- 創(chuàng)建Employee表 --CREATE TABLE Employee(  Id int(4) not null,  Name VARCHAR(10) not null,  Salary int(10) not null,  DepartmentId int(4) not NULL);
-- 增加4條數(shù)據(jù) --INSERT INTO Employee VALUES(1,'Joe',70000,1);INSERT INTO Employee VALUES(2,'Henry',80000,2);INSERT INTO Employee VALUES(3,'Sam',60000,2);INSERT INTO Employee VALUES(4,'Max',90000,1);

創(chuàng)建Department 表，包含公司所有部門的信息。

-- 創(chuàng)建Department表 --CREATE TABLE Department(  Id int(4) not null,  Name VARCHAR(10));
-- 增加2條數(shù)據(jù) --INSERT into Department VALUES(1,'IT');INSERT?into?Department?VALUES(2,'Sales');

問題一：編寫一個 SQL 查詢，找出每個部門工資最高的員工。例如，根據(jù)上述給定的表格，Max 在 IT 部門有最高工資，Henry 在 Sales 部門有最高工資。

--?通過排序（必須加上LIMIT，不然結(jié)果不正確）與GROUP?BY結(jié)合求出部門內(nèi)最高工資，INNER?JOIN再關(guān)聯(lián)上部門名?--SELECT  D.Name AS Department,  E.Name AS Employee,  Salary FROM  ( SELECT * FROM Employee ORDER BY Salary DESC LIMIT 10 ) AS E  INNER JOIN Department AS D ON E.DepartmentId = D.Id GROUP BY  E.DepartmentId;

問題2：查詢部門工資前3的信息。

-- 增加2條數(shù)據(jù) --INSERT INTO Employee VALUES(5,'Janet',69000,1);INSERT?INTO?Employee?VALUES(6,'Randy',85000,1);
-- 使用窗口函數(shù)對各部門的員工工資進(jìn)行分組排序SELECT  ( SELECT D.Name FROM Department AS D WHERE DepartmentId = D.Id ) AS 'Department',??Name,  Salary FROM  ( SELECT Name, Salary, DepartmentId, RANK() over ( PARTITION BY DepartmentId ORDER BY Salary DESC ) AS ranking FROM Employee ) AS b WHERE  ranking <= 3;