力扣数据库题目

weixin_43298992

已于 2022-11-21 10:55:40 修改

阅读量282

点赞数

文章标签：数据库力扣

于 2022-11-09 13:32:22 首次发布

本文链接：https://blog.csdn.net/weixin_43298992/article/details/127768141

版权

1. rank() over (排序依据列)

2. dense_rank() over (排序依据列)

3. row_number() over (排序依据列)

用传统方式来排序

连续出现的数字

join连接

组合两个数据库，但有的数据找不到对应的（不是一一对应，但必须显示全部信息）给没有对应信息的空着。

使用左右连接 left join on 可以满足，如果没有某个人的地址信息，使用 where 子句过滤记录将失败，因为它不会显示姓名信息

left join （左连接，左外连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。

right join （右连接，右外连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。

full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。

排序查找第几个数据

LIMIT ：限制输出几行。

offset：跳过几行。

distinct：过滤多余的重复记录只保留一条（必须放在开头）

order by ：按某一列排序，默认升序排列

top ：输出前几名的数据

SELECT DISTINCT
Salary AS SecondHighestSalary
FROM
Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1

Select distinct(task_id),taskid from task;

Distinct用法

a.在count计算不重复的记录的时候能用到
比如SELECT COUNT( DISTINCT player_id ) FROM task;
就是计算talbebname表中id不同的记录有多少条

b,在需要返回记录不同的id的具体值的时候可以用
比如SELECT DISTINCT player_id FROM task;
返回talbebname表中不同的id的具体的值

c.上面的情况2对于需要返回mysql表中2列以上的结果时会有歧义
比如SELECT DISTINCT player_id, task_id FROM task;
实际上返回的是player_id与task_id同时不相同的结果,也就是DISTINCT同时作用了两个字段，必须得player_id与task_id都相同的才被排除了,与我们期望的结果不一样,我们期望的是player_id不同被过滤

在这种情况下，distinct同时作用了两个字段，player_id,task_id

d.这时候可以考虑使用group_concat函数来进行排除,不过这个mysql函数是在mysql4.1以上才支持的

e. 其实还有另外一种解决方式,就是使用
SELECT player_id, task_id, count(DISTINCT player_id) FROM task.
虽然这样的返回结果多了一列无用的count数据(有时也许就需要这个数据)

f 同时我们还可以利用下面的方式解决b遇到的歧义问题通过group by 分组

select player_id,task_id from task group by player_id

分数排名

对于相同分数的排名相同

使用小提示
dense_rank() over 后面跟排序的依据的列，下面是用了一个排序好的列(order by score desc)。
注意：如果select中有一列是用rank()这类函数，其他的列都会按着他这列规定好的顺序排。

1. rank() over (排序依据列)

作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
说明：例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。即：1 1 3 4 5 5 7

2. dense_rank() over (排序依据列)

作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6

3. row_number() over (排序依据列)

作用：查出指定条件后的进行排名，条件相同排名也不相同，排名间断不连续。
说明：这个函数不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序。即：1 2 3 4 5 6

用传统方式来排序

最后的结果包含两个部分，第一部分是降序排列的分数，第二部分是每个分数对应的排名。

第一部分不难写：
select a.Score as Score from Scores a order by a.Score DESC
比较难的是第二部分。假设现在给你一个分数X，如何算出它的排名Rank呢？
我们可以先提取出大于等于X的所有分数集合H，将H去重后的元素个数就是X的排名。

比如你考了99分，但最高的就只有99分，那么去重之后集合H里就只有99一个元素，个数为1，因此你的Rank为1。
先提取集合H：select b.Score from Scores b where b.Score >= X;
我们要的是集合H去重之后的元素个数，因此升级为：
select count(distinct b.Score) from Scores b where b.Score >= X as Rank;
而从结果的角度来看，第二部分的Rank是对应第一部分的分数来的，所以这里的X就是上面的a.Score，把两部分结合在一起为：
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC

连续出现的数字

一个表查询连续出现N次的数组。

当次数较少时，且id连续的时候，可以用N个表来比较连续的行的数字

当次数较多时，id不连续的时候，

1，根据原始数据编号id，排序。（row_number() over(表达式)）使用Id来排序既row_number() over(order by Id)

SELECT Id,Num, row_number() over(order by id) as SerialNum FROM ContinueNumber

2，根据原始数据数字进行排序，将数字值一样的分组排序，row_number() over(表达式)，参数：（num分组，id排序）row_number() over(partition by num order by id)

SELECT Id,Num,ROW_NUMBER() over(partition by Num order by Id) as SerialGroup FROM ContinueNumber

3，两个列（SerialNum，SerialGroup）对应相减，只要连续，相减得到的值是一样的。不连续相减得到的值也不同。

SELECT Id,Num,
row_number() over(order by id) -
row_number() over(partition by Num order by Id) as SerialNumberSubGroup
FROM ContinueNumber

4，拿到Num,就是求得的数据,去重（distinct）指：有可能同一个数字在多处出现三次以上。

SELECT DISTINCT Num FROM (
SELECT Num,COUNT(1) as SerialCount FROM
(SELECT Id,Num,
row_number() over(order by id) -
ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroup
FROM ContinueNumber) as Sub
GROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) >= 3) as Result

Mysql in 操作

SELECT 
    column1,column2,...
FROM
    table_name
WHERE 
 (expr|column_1) IN ('value1','value2',...);

示例：

WHERE country IN ('USA' , 'France');
WHERE country = 'USA' OR country = 'France';
WHERE country NOT IN ('USA' , 'France');
子查询

SELECT 
    orderNumber, customerNumber, status, shippedDate
FROM
    orders
WHERE
    orderNumber IN (SELECT //子查询，查出来的是一个子表，
            orderNumber
        FROM
            orderDetails
        GROUP BY orderNumber
        HAVING SUM(quantityOrdered * priceEach) > 60000);

//力扣数据库题目184部门最高工资的员工
SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',              2，根据每个部门最高工资和部门Id去查询
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)          1，查询每个部门最高的工资
        FROM
            Employee
        GROUP BY DepartmentId
	)
;