SQL_Task

创建表1

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(255), 
  Salary INT,
  DepartmentId INT
);

INSERT INTO Employee VALUES
  (1, 'Joe', 70000, 1),
  (2, 'Henry', 80000, 2),
  (3, 'Sam', 60000, 2),
  (4, 'Max', 90000, 1);

创建表2

CREATE TABLE Department (
  Id INT PRIMARY KEY,
  Name VARCHAR(255)
);

INSERT INTO Department VALUES
  (1, 'IT'),
  (2, 'Sales');

 查询

SELECT
    d. NAME AS Department,
    e. NAME AS Employee,
    e.Salary as Salary
FROM
    employee as e
LEFT JOIN     department as d
on d.Id = e.DepartmentId 
WHERE (e.DepartmentId, e.Salary) IN
  (SELECT DepartmentId, MAX(Salary)
   FROM Employee
   GROUP BY DepartmentId);

Task3

 结果

排序1(rank1):

SELECT class, score_avg, RANK() OVER (ORDER BY score_avg DESC) AS rank1 FROM scores;

使用 RANK() 排序,分数相同并列第一。

排序2(rank2):

SELECT class, score_avg, DENSE_RANK() OVER (ORDER BY score_avg DESC) AS rank2 FROM scores;

使用 DENSE_RANK() 排序,分数相同并列第一。

排序3(rank3):

SELECT class, score_avg, ROW_NUMBER() OVER (ORDER BY score_avg DESC) AS rank3 FROM scores;

使用 ROW_NUMBER() 排序,按顺序排名。

以上使用 SQL 窗口函数可以实现给定的 3 种排名规则。

注意不同数据库对窗口函数的支持情况,这里使用的是通用的窗口函数语法