SQL_leetcode整理

目录

 

1.Not Boring Movies（620）

题目描述

题目类型

样例参考

代码思路

代码

测试结果

2.Rank Scores（178）

题目描述

题目类型

样例参考

思路

代码

测试结果

3.Delete Duplicate Emails（196）

题目描述

题目类型

样例参考

思路

代码

测试结果

4.Consecutive Numbers（180）

题目描述

题目类型

样例参考

思路

代码

测试结果

5.Human Traffic of Stadium（601）

题目描述

题目类型

样例介绍

思路

代码

测试结果

6.Department Highest Salary（184）

题目描述

题目类型

样例介绍

思路

代码

测试结果

---

1.Not Boring Movies（620）

题目描述

X市新开了一家电影院，很多人都想去这家电影院。电影院还会发放一张海报，标明电影的评级和描述。

请编写一个SQL查询来输出ID为奇数且描述不“无聊”的电影。通过评级对结果排序。

题目类型

单表查询

样例参考

 

代码思路

观察样例我们可知该题为单表查询，只要满足相应条件就可以输出满足条件的所需属性，该题全部属性都要输出，设置条件为ID为奇数（id%2=1）且评价结果不要等于‘boring’，还有一个要求就是要按评价等级降序排列，可以用order by rating desc 实现

代码

/*MS SQL Sever代码如下*/
select * from cinema                    --查询满足条件的所有属性
where id%2=1 and description<>'boring'  --选择满足ID为奇数且评价为步“无聊”的元组
order by rating desc                    --按评级结果降序排列

测试结果

2.Rank Scores（178）

题目描述

编写一个SQL查询来对分数进行排序。如果两个分数之间存在平手，那么它们的排名应该是相同的。注意，在平手之后，下一个排名数字应该是下一个连续的整数值。换句话说，等级之间不应该有“漏洞”。

题目类型

排序选择

样例参考

思路

题目意思是要将分数按从大到小排序，且对应的排名要正确，分数利用order by可以很容易实现从小到大的排序，难点在于要新建一个属性列rank，且里面的每一组要与score联系，我的思路是构建一个新的属性rank，属性rank的值根据所在组的score的与整个表的做大于等于判断统计出count值作为rank的值。

代码

SELECT Score,                          
(  SELECT COUNT(distinct Score)     --distinct的作用是取消指定列的重复值        
   FROM Scores                        
   WHERE Score >= s.Score           --取分数与前面取得Score分数做比较     
)  as Rank                          --构建新属性rank
 
FROM Scores s order by Score Desc           

                         

测试结果

3.Delete Duplicate Emails（196）

题目描述

编写一个SQL查询来删除一个名为Person的表中所有重复的电子邮件条目，只保留基于其最小Id的惟一电子邮件。

题目类型

条件删除

样例参考

思路

1.删除操作，创建两个Person表参数，选定一个拿去删除，设置条件为email相同时Id大的删除

2.选择操作，利用group by的去除冗余和性质，去除冗余，并使用min选择小id，最后再根据id大小顺序排列

代码

/*MySQL*/
DELETE p                             ##在p表中删除                     
from Person p, Person q              ##创建表p与q
where p.Id>q.Id AND p.Email=q.Email  ##删除Email相同且Id>大于与自己email相同对应的Id所在列

/*MS SQL Sever*/ 
select *--选择全部参数
from (  select min(Person$.Id) as Id ,Person$.Email --选择Eamil最小的 id和相应的email值
        from Person$                                --从person表中
        group by Person$.Email)                     --email相同为一个组
                               as Person_1$         --构建新表Person_1
order by Person_1$.Id                               --在新表中按id顺序排列

测试结果

leetcode测试结果

sql测试结果

 

4.Consecutive Numbers（180）

题目描述

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

题目类型

多表查询

样例参考

思路

本题关键的点在于如何判断有连续的至少三个的相同的数字，我的思路构建三个相同的表做笛卡尔积，通过连续的数字相同的特性，设置条件：当为连续id是，各个id对应的num是否相等。

代码

Select DISTINCT l1.Num as ConsecutiveNums     --选择l1构建新表属性值为ConsecutiveNums
from Logs l1, Logs l2, Logs l3                --构建三个log表
--判断条件当为连续id是，各个id对应的num是否相等，在该题中因为不要求输出全部的满足条件的id，所以只要判断各个id对应的num是否相等即可
where (l1.Id=l2.Id-1 and l2.Id=l3.Id-1)      
        and (l1.Num=l2.Num and l2.Num=l3.Num)

测试结果

leetcode测试结果

sql测试结果

5.Human Traffic of Stadium（601）

题目描述

X city新建了一个体育场，每天都有很多人去参观，统计数据被保存为以下列:id, visit_date, people

请写一个查询来显示连续3行或3行以上的记录和超过100人(含)的人数。

题目类型

多表查询

样例介绍

思路

这题的思路与第四题的思路相似，目的是查询连续3行或3行以上的记录和超过100人(含)的人数，但不同的是题目输出的是满足条件的所有组，则不能漏过任何一组，则要采用最保险的办法列出所有满足连续3行或3行以上的记录和超过100人(含)的人数的情况。

代码

SELECT  s1.*                                             
FROM stadium$ as s1, stadium$ as s2, stadium$ as s3      --构建表s1,s2,s3
WHERE 
    --根据要求输出全部的满足条件的列，所以列出所有情况   
    ((s1.id + 1 = s2.id AND s1.id + 2 = s3.id)
    OR 
    (s1.id - 1 = s2.id AND s1.id + 1 = s3.id)
    OR
    (s1.id - 2 = s2.id AND s1.id - 1 = s3.id)
    )                                                    
   --三个连续id的people值都要不小于100
    AND s1.people>=100                                        
    AND s2.people>=100
    AND s3.people>=100                                   
​
group BY s1.id,s1.visit_date,s1.people         
--为了输出全部属性sql sever代码必须要将所有属性全都group by

SELECT s1.* FROM stadium s1, stadium s2, stadium s3    #构建表s1,s2,s3
​
    WHERE 
    --根据要求输出全部的满足条件的列，所以列出所有情况 
    ((s1.id + 1 = s2.id
    AND s1.id + 2 = s3.id)
    OR 
    (s1.id - 1 = s2.id
    AND s1.id + 1 = s3.id)
    OR
    (s1.id - 2 = s2.id
    AND s1.id - 1 = s3.id)
    )   
    #三个连续id的people值都要不小于100
    AND s1.people>=100 
    AND s2.people>=100
    AND s3.people>=100                                
​
GROUP BY s1.id                                      
#为了输出全部属性my sql代码只需group by的一个属性就可以将对应组输出

 

测试结果

Leetcode测试结果

SQL测试结果

 

6.Department Highest Salary（184）

题目描述

Employee表包含所有员工。每个员工都有一个Id，一个工资，还有一个部门Id列。部门表包含公司的所有部门。

编写一个SQL查询，查找每个部门中工资最高的员工。对于上面的表，您的SQL查询应该返回以下行(行顺序无关紧要)。

题目类型

多表链接，条件查询

样例介绍

思路

因为生成的表的属性包括两个表中的内容所以要连接两个表，可以使用笛卡尔也可以使用内连接，判断条件为量表中的公司id是否相等，还有一个条件就是挑选出最大值，着可以应用到聚合函数MAX（），选出各个公司中最高薪资的员工id及其相关属性。

代码

select D.Name as Department, E.Name as Employee, E.Salary as Salary
from Employee E inner join Department D                                  --两表内连接
on E.DepartmentId = D.Id and                                             
--取出Employee表中的DepartmentId与Department中Id相等的元组
E.Salary=(select MAX(Salary) from Employee E1 where E1.DepartmentId=D.Id)
--且取每个公司的工资最大值d

测试结果