-- 使用开窗函数 select round(sum(tiv_2016), 2) as tiv_2016 from ( select *, count(pid) over(partitionby tiv_2015) as rk1, count(pid) over(partitionby concat(lat, lon)) as rk2 from Insurance ) as tmp where rk1<>1and rk2=1
+-------------+----------+ | Column Name | Type | +-------------+----------+ | id | int | | client_id | int | | driver_id | int | | city_id | int | | status | enum | | request_at | date | +-------------+----------+ id 是这张表的主键。 这张表中存所有出租车的行程信息。每段行程有唯一 id ,其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。 status 是一个表示行程状态的枚举类型,枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。
表:Users
1 2 3 4 5 6 7 8 9 10
+-------------+----------+ | Column Name | Type | +-------------+----------+ | users_id | int | | banned | enum | | role | enum | +-------------+----------+ users_id 是这张表的主键。 这张表中存所有用户,每个用户都有一个唯一的 users_id ,role 是一个表示用户身份的枚举类型,枚举成员为 (‘client’, ‘driver’, ‘partner’) 。 banned 是一个表示用户是否被禁止的枚举类型,枚举成员为 (‘Yes’, ‘No’) 。
+--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ Id是该表的主键列。 departmentId是Department表中ID的外键。 该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。
表: Department
1 2 3 4 5 6 7 8
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ Id是该表的主键列。 该表的每一行表示部门ID和部门名。
输入: Employee 表: +----+-------+--------+--------------+ | id | name | salary | departmentId | +----+-------+--------+--------------+ | 1 | Joe | 85000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | | 7 | Will | 70000 | 1 | +----+-------+--------+--------------+ Department 表: +----+-------+ | id | name | +----+-------+ | 1 | IT | | 2 | Sales | +----+-------+ 输出: +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Joe | 85000 | | IT | Randy | 85000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+ 解释: 在IT部门: - Max的工资最高 - 兰迪和乔都赚取第二高的独特的薪水 - 威尔的薪水是第三高的
在销售部: - 亨利的工资最高 - 山姆的薪水第二高 - 没有第三高的工资,因为只有两名员工
CodeDemo
1 2 3 4 5 6 7 8 9 10 11 12
# Write your MySQL query statement below # 分组求TopN是窗口函数的应用 select Department,Employee,Salary from ( select d.name as Department, e.name as Employee, e.salary as Salary, dense_rank() over(partitionby departmentId orderby salary desc) as dr from Employee e join Department d on e.departmentId = d.id ) as tmp where dr <=3;
输入: Employee 表: +----+-------+--------+--------------+ | id | name | salary | departmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | +----+-------+--------+--------------+ Department 表: +----+-------+ | id | name | +----+-------+ | 1 | IT | | 2 | Sales | +----+-------+ 输出: +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Jim | 90000 | | Sales | Henry | 80000 | | IT | Max | 90000 | +------------+----------+--------+ 解释:Max 和 Jim 在 IT 部门的工资都是最高的,Henry 在销售部的工资最高。
CodeDemo
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
# Write your MySQL query statement below select Department, Employee, Salary from ( select e.id, e.name as Employee, e.salary as Salary, e.departmentId, d.name as Department, dense_rank() over(partitionby departmentId orderby salary desc) dr from Employee e join Department d on d.id = e.departmentId ) tmp where dr =1
# 这里的delete是借用官方的案例, delete t1是删除t1中跟t2表不匹配的字段, 满足条件就删除 delete t1 from Person t1, Person t2 where t1.email = t2.email and t1.id > t2.id # 自连接会出现笛卡尔积, 也就是9条记录, 但是这个解题测试不会显示