数据库习题练习-----基础篇(一)

练习题1 查询第二高的薪水

leetcode 171题：
    查询第二高的薪水（salary）

----------------

 ID      salary

 1         100

 2          200

 3          300

----------------

------------

secondHighestsalary

 200

------------
#解答
#max方法
SELECT MAX(Salary) 第二大工资 FROM employee WHERE Salary<(SELECT MAX(Salary) FROM employee)
#排序
SELECT IFNULL((SELECT Salary 第二大工资 FROM Employee ORDER BY Salary DESC LIMIT 1,1),NULL) 第二大工资

---

---

练习题2 查询第n高的薪水

使用group by 即可过滤重复工资

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set n = N-1;
  RETURN (
      # Write your MySQL query statement below.
      ifnull((select Salary from Employee group by Salary order by Salary desc limit n,1),null)
  );
END

---

---

练习题3 通过SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。

*+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+*

#3.1通过分组去重,使用@rownum:=@rownum+1进行排序
SELECT sc.`Scores` Score, rr.rank_no Rank FROM Scores sc LEFT JOIN (

(SELECT a.* ,@rownum:=@rownum + 1 AS rank_no FROM(SELECT s.`Scores`  Scores FROM Scores s GROUP BY s.Scores ORDER BY s.Scores DESC) a ,
(SELECT @rownum := 0) r) rr 

) ON  sc.`Scores` = rr.`Scores` ORDER BY Score DESC 
################################################################
#3.2#查询出<=该数字的数量并使用distinct进行去重操作
SELECT 
a.Scores,
	(SELECT COUNT(DISTINCT(Scores)) FROM scores temp WHERE a.Scores<=temp.Scores ORDER BY Scores  DESC) 排序号 
FROM scores a ORDER BY Scores DESC

---

---

练习题4 编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+
------>给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

#通过id判断连续三个数字
SELECT 
	DISTINCT(a.`Num`) 
FROM 
	`logs` a,`logs` b,`logs` c 
WHERE
	a.`Id`-b.`Id`=2 AND c.`Id`-b.`Id`=1 AND a.`Num`=b.`Num` AND b.`Num` = c.`Num`

---

---

练习题5 部门工资最高的员工(链接)

Employee
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
Departmen
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
------>编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

DROP TABLE IF EXISTS `department`;CREATE TABLE `department` (  `Id` int(11) NOT NULL AUTO_INCREMENT,  `Name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*Data for the table `department` */LOCK TABLES `department` WRITE;insert  into `department`(`Id`,`Name`) values (1,'IT'),(2,'Sales');UNLOCK TABLES;/*Table structure for table `employee` */DROP TABLE IF EXISTS `employee`;CREATE TABLE `employee` (  `Id` int(11) NOT NULL AUTO_INCREMENT,  `Name` varchar(20) DEFAULT NULL,  `Salary` int(11) DEFAULT NULL,  `DepartmentId` int(11) DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;/*Data for the table `employee` */LOCK TABLES `employee` WRITE;insert  into `employee`(`Id`,`Name`,`Salary`,`DepartmentId`) values (1,'Joe',7,1),(2,'H',8,2),(3,'s',6,2),(4,'M',9,1);UNLOCK TABLES;

解答

#通过分组获取最高工资和部门id,然后作为条件查询Employee表
    SELECT 
      de.`Name` Department,
      em.Name Employee,
      em.`Salary` Salary 
    FROM
      Employee em,
      Department de,
      (SELECT 
        departmentId,
        MAX(Salary) sa 
      FROM
        employee 
      GROUP BY DepartmentId) temp 
    WHERE em.`Departmentid` = temp.`DepartmentId` 
      AND de.`Id` = em.`DepartmentId` 
      AND em.Salary = temp.sa 

---

---

练习题6 部门工资前三高的所有员工(链接)

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            |
+----+-------+--------+--------------+
Departmen
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
------>编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

DROP TABLE IF EXISTS `department`;CREATE TABLE `department` (  `Id` int(11) NOT NULL AUTO_INCREMENT,  `Name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;/*Data for the table `department` */LOCK TABLES `department` WRITE;insert  into `department`(`Id`,`Name`) values (1,'IT'),(2,'Sales');UNLOCK TABLES;/*Table structure for table `employee` */DROP TABLE IF EXISTS `employee`;CREATE TABLE `employee` (  `Id` int(11) NOT NULL AUTO_INCREMENT,  `Name` varchar(20) DEFAULT NULL,  `Salary` int(11) DEFAULT NULL,  `DepartmentId` int(11) DEFAULT NULL,  PRIMARY KEY (`Id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;/*Data for the table `employee` */LOCK TABLES `employee` WRITE;insert  into `employee`(`Id`,`Name`,`Salary`,`DepartmentId`) values (1,'Joe',85,1),(2,'H',80,2),(3,'s',60,2),(4,'M',90,1),(5,'Janet',69,1),(6,'Randy',85,1),(7,'will',70,1);UNLOCK TABLES;

解答

#通过distinct去重获取比e1大的数的数量
SELECT 
  de.name Department,
  e1.name Employee,
  e1.salary Salary 
FROM
  employee e1 
  JOIN Department de 
    ON de.id = e1.DepartmentId 
WHERE 3 > 
  (SELECT 
    COUNT(DISTINCT e2.salary) 
  FROM
    Employee e2 
  WHERE e1.salary < e2.salary 
    AND e1.departmentId = e2.departmentId) 
ORDER BY de.id ASC, e1.salary DESC

---

---

更新时间2020年3月20日
持续更新ing…