![](https://img-blog.csdnimg.cn/20201014180756913.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
Leetcode数据库题解
徐周
这个作者很懒,什么都没留下…
展开
-
Leetcode数据库题解-175. Combine Two Tables
题目地址:https://leetcode.com/problems/combine-two-tables/样例中有些人的 PersonId 无法在 Address 表中找到,所以使用 LEFT JOIN.SELECT FirstName, LastName, City, StateFROM AddressJOIN PersonON Person.PersonId=Address....原创 2019-02-01 10:10:51 · 126 阅读 · 0 评论 -
Leetcode数据库题解-620. Not Boring Movies
题目地址:https://leetcode.com/problems/not-boring-movies/题解:查询电影ID为奇数且description不是‘boring’的字段select * from cinemawhere id%2=1and description != 'boring'order by rating desc...原创 2019-06-22 22:23:43 · 139 阅读 · 0 评论 -
Leetcode数据库题解-596. Classes More Than 5 Students
题目地址:https://leetcode.com/problems/classes-more-than-5-students/题解:列出被5名及以上的学生选的课程。根据class进行分组,然后统计超过5名及以上学生选择课程。select distinct classfrom coursesgroup by classhaving count(distinct student)&g...原创 2019-06-22 22:07:04 · 139 阅读 · 0 评论 -
Leetcode:找出数组中重复元素的题目总结
leetcode中找出数组元素的题目有如下几题:645.Set Mismatch:https://leetcode.com/problems/set-mismatch/description/448.Find All Numbers Disappeared in an Array:https://leetcode.com/problems/find-all-numbers-disapp...原创 2019-05-16 09:47:51 · 1434 阅读 · 0 评论 -
Leetcode数据库题解-626. Exchange Seats
题目地址:https://leetcode.com/problems/exchange-seats/题目:玛丽编写一个查询以改变邻近学生的座位,如果学生人数是奇数,则不需要改变最后一个座位。与其变换名称,不如将id为2n变换为2n-1,2n-1变换为2n (n=1,2,3....)。第一种:该方法通不过测试,因为是两个sql语句。update seat t1, seat t2set...原创 2019-02-13 14:13:57 · 198 阅读 · 0 评论 -
Leetcode数据库题解-601. Human Traffic of Stadium
题目地址:https://leetcode.com/problems/human-traffic-of-stadium/题解:如下代码select distinct t1.*from stadium t1, stadium t2, stadium t3where t1.people>99 and t2.people>99 and t3.people>99 ...原创 2019-02-13 14:14:25 · 188 阅读 · 0 评论 -
Leetcode数据库题解-595. Big Countries
题目地址:https://leetcode.com/problems/big-countries/题解:直接查找,很简单。select name,population,areafrom Worldwhere area>3000000 or population>25000000;注意count里需要加上distinct,我一开始想的是应该没有重复的,但是提交失败了...原创 2019-02-13 14:14:42 · 158 阅读 · 0 评论 -
Leetcode数据库题解-197. Rising Temperature
题目地址:https://leetcode.com/problems/rising-temperature/题解:第一种解法是借助Datediff()函数,该函数用于返回两个日期之间的天数。语法:DATEDIFF(date1,date2)date1 和 date2 参数是合法的日期或日期/时间表达式。select w1.Id as Idfrom Weather w1,Weather w...原创 2019-02-13 14:14:58 · 138 阅读 · 0 评论 -
Leetcode数据库题解-185. Department Top Three Salaries
题目地址:https://leetcode.com/problems/department-top-three-salaries/题解:编写一个SQL查询来查找在每个部门中获得前三名工资的员工。建立查询语句重点是要拆分业务逻辑,通过将Employee e1表中的薪水与Employee e2表中的数据比较统计出排名前列的数据。其中3可以改成任意数值,表示求出各个分组中排名前几的数据。sel...原创 2019-02-01 14:47:18 · 199 阅读 · 0 评论 -
Leetcode数据库题解-184. Department Highest Salary
题目地址:https://leetcode.com/problems/department-highest-salary/题解:根据部门id连接Employee表和Department表。然后对于每条记录,从另一个 Employee表中找出对应部门的最高薪水,并判断该员工的薪水是否等于部门最高薪水。select d.Name as Department,e.Name as Employe...原创 2019-02-01 11:45:44 · 162 阅读 · 0 评论 -
Leetcode数据库题解-183. Customers Who Never Order
题目地址:https://leetcode.com/problems/customers-who-never-order/题解:选出从未订单的客户名字,很容易想到NOT IN操作,只要选出Coustmors ID不在Orders cunstomrId内的客户。方法一:select Name as Customersfrom Customerswhere Id not in(se...原创 2019-02-01 10:49:05 · 112 阅读 · 0 评论 -
Leetcode数据库题解-182. Duplicate Emails
题目地址:https://leetcode.com/problems/duplicate-emails/题解:找到重复的email,采用HAVING找出数量大于1的email即可。select Email from Persongroup by Emailhaving count(Email)>1;补充:这里的只能使用having而不能使用where,原因是我们是要对gro...原创 2019-02-01 10:43:24 · 130 阅读 · 0 评论 -
Leetcode数据库题解-181. Employees Earning More Than Their Managers
题目地址:https://leetcode.com/problems/employees-earning-more-than-their-managers/题解:找到上司的薪水直接比较,通过两个select实现。select Name as Employeefrom Employee where Salary > (select Salaryfrom Employee as...原创 2019-02-01 10:29:33 · 141 阅读 · 0 评论 -
Leetcode数据库题解-180. Consecutive Numbers
题目地址:https://leetcode.com/problems/consecutive-numbers/题解:这题很简单,直接暴力查询。select distinct l1.Num as ConsecutiveNumsfrom Logs l1,Logs l2,Logs l3where l1.Id+1=l2.Id and l2.Id+1=l3.Idand l1.Num=l2.N...原创 2019-02-01 10:27:45 · 195 阅读 · 0 评论 -
Leetcode数据库题解-177. Nth Highest Salary
题目地址:https://leetcode.com/problems/nth-highest-salary/题解:这道题是之前那道Second Highest Salary的拓展,根据之前那道题的做法,我们可以很容易的将其推展为N,根据对Second Highest Salary中解法一的分析,我们只需要将OFFSET后面的1改为N-1就行了,但是这样MySQL会报错,估计不支持运算,那么我们...转载 2019-02-01 10:25:53 · 142 阅读 · 0 评论 -
Leetcode数据库题解-178. Rank Scores
题目地址:https://leetcode.com/problems/rank-scores/SELECT Score, ( SELECT COUNT(DISTINCT Score) FROM Scores AS c WHERE o.Score <= c.Score ) AS RankFROM Scores AS oORDER BY Score ...原创 2019-02-01 10:23:31 · 432 阅读 · 0 评论 -
Leetcode数据库题解-176. Second Highest Salary
题目地址https://leetcode.com/problems/second-highest-salary/题解:这道题让我们找表中某列第二大的数,这道题有很多种解法。第一种:先来看一种使用Limit和Offset两个关键字的解法,MySQL中Limit后面的数字限制了我们返回数据的个数,Offset是偏移量,那么如果我们想找第二高薪水,我们首先可以先对薪水进行降序排列,然后我们将Of...原创 2019-02-01 10:20:51 · 354 阅读 · 0 评论 -
Leetcode数据库题解-627. Swap Salary
题目地址:https://leetcode.com/problems/swap-salary/题解:交换salary表中的sex,用到if语句,解释一下,if(a,b,c):如果a成立则执行b,否则执行cupdate salary set sex = if(sex='m','f','m')...原创 2019-06-22 22:49:16 · 137 阅读 · 0 评论