mysql练习 --题目--答案--视频讲解--( lk vip)

mysql练习

01-hiving对分组数据进行筛选

01-建表语句

Create table If Not Exists Employee (Id int, Name varchar(255), Department varchar(255), ManagerId int);

insert into Employee (Id, Name, Department, ManagerId) values ('101', 'John', 'A', null);
insert into Employee (Id, Name, Department, ManagerId) values ('102', 'Dan', 'A', '101');
insert into Employee (Id, Name, Department, ManagerId) values ('103', 'James', 'A', '101');
insert into Employee (Id, Name, Department, ManagerId) values ('104', 'Amy', 'A', '101');
insert into Employee (Id, Name, Department, ManagerId) values ('105', 'Anne', 'A', '101');
insert into Employee (Id, Name, Department, ManagerId) values ('106', 'Ron', 'B', '101');

02-题目

 Employee 表包含所有员工和他们的经理。每个员工都有一个 Id，并且还有一列是经理的 Id。
 ​
 +------+----------+-----------+----------+
 |Id    |Name      |Department |ManagerId |
 +------+----------+-----------+----------+
 |101   |John      |A          |null      |
 |102   |Dan       |A          |101       |
 |103   |James     |A          |101       |
 |104   |Amy       |A          |101       |
 |105   |Anne      |A          |101       |
 |106   |Ron       |B          |101       |
 +------+----------+-----------+----------+
 给定 Employee 表，请编写一个SQL查询来查找至少有5名直接下属的经理。对于上表，您的SQL查询应该返回：
 ​
 +-------+
 | Name  |
 +-------+
 | John  |
 +-------+

03-答案：

 SELECT
     Name
 FROM
     Employee AS t1 JOIN
     (SELECT
         ManagerId
     FROM
         Employee
     GROUP BY ManagerId
     HAVING COUNT(ManagerId) >= 5) AS t2
     ON t1.Id = t2.ManagerId
 ;

02-中位数计算_sum窗口函数

01-建表语句

 Create table If Not Exists Numbers (Number int, Frequency int);

 insert into Numbers (Number, Frequency) values ('0', '7');
 insert into Numbers (Number, Frequency) values ('1', '1');
 insert into Numbers (Number, Frequency) values ('2', '3');
 insert into Numbers (Number, Frequency) values ('3', '1');

02-题目

Numbers 表保存数字的值及其频率。
 ​
 +----------+-------------+
 |  Number  |  Frequency  |
 +----------+-------------|
 |  0       |  7          |
 |  1       |  1          |
 |  2       |  3          |
 |  3       |  1          |
 +----------+-------------+
 在此表中，数字为 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3，所以中位数是 (0 + 0) / 2 = 0
 ​
 +--------+
 | median |
 +--------|
 | 0.0000 |
 +--------+
 请编写一个查询来查找所有数字的中位数并将结果命名为 median 

03-答案

 select avg(number) median
 from
    (select number,
         sum(frequency) over(order by number) asc_accumu,
         sum(frequency) over(order by number desc) desc_accumu,
         sum(frequency) over() total
     from Numbers) t1
 where asc_accumu >= total / 2 and desc_accumu >= total / 2

03-查询累计薪资

01-建表语句

DROP TABLE IF EXISTS employee;
Create table If Not Exists Employee (Id int, Month int, Salary int);

insert into Employee(Id, Month, Salary) values ('1', '1', '20');
insert into Employee(Id, Month, Salary) values ('2', '1', '20');
insert into Employee(Id, Month, Salary) values ('1', '2', '30');
insert into Employee(Id, Month, Salary) values ('2', '2', '30');
insert into Employee(Id, Month, Salary) values ('3', '2', '40');
insert into Employee(Id, Month, Salary) values ('1', '3', '40');
insert into Employee(Id, Month, Salary) values ('3', '3', '60');
insert into Employee(Id, Month, Salary) values ('1', '4', '60');
insert into Employee(Id, Month, Salary) values ('3', '4', '70');

02-题目

Employee 表保存了一年内的薪水信息。

请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。

结果请按 Id 升序，然后按 Month 降序显示。

 
示例：
输入：

| Id | Month | Salary |
|----|-------|--------|
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 1  | 2     | 30     |
| 2  | 2     | 30     |
| 3  | 2     | 40     |
| 1  | 3     | 40     |
| 3  | 3     | 60     |
| 1  | 4     | 60     |
| 3  | 4     | 70     |
输出：

| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 3  | 3     | 100    |
| 3  | 2     | 40     |

解释：

员工 '1' 除去最近一个月（月份 '4'），有三个月的薪水记录：月份 '3' 薪水为 40，月份 '2' 薪水为 30，月份 '1' 薪水为 20。

所以近 3 个月的薪水累计分别为 (40 + 30 + 20) = 90，(30 + 20) = 50 和 20。

| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
员工 '2' 除去最近的一个月（月份 '2'）的话，只有月份 '1' 这一个月的薪水记录。

| Id | Month | Salary |
|----|-------|--------|
| 2  | 1     | 20     |
员工 '3' 除去最近一个月（月份 '4'）后有两个月，分别为：月份 '3' 薪水为 60 和 月份 '2' 薪水为 40。所以各月的累计情况如下：

| Id | Month | Salary |
|----|-------|--------|
| 3  | 3     | 100    |
| 3  | 2     | 40     |

03-答案

SELECT t.id, t.`month`, t.salary FROM
        (SELECT id, `month`,
                 SUM(salary) over(PARTITION BY id ORDER BY `month`) salary,
                 rank() over(PARTITION BY id ORDER BY `month` DESC) ranks
         FROM Employee) t
 WHERE t.ranks > 1;