leetcode database题目
LeetCode有10道SQL的题目,最近学习SQL语言,顺便刷题强化一下,
说实话刷完SQL学习指南这本书,不是很难,上面的例子 跟语法规则我都能理解透,
实际中来做一些比较难的业务逻辑题,却一下子很难想起如何利用SQL来描述
还是要多刷题开阔自己的思路,把学到手的语法加以练习 应用到实际中去
197. Rising Temperature
Given a Weather
table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
+---------+------------+------------------+ | Id(INT) | Date(DATE) | Temperature(INT) | +---------+------------+------------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +---------+------------+------------------+
For example, return the following Ids for the above Weather table:
+----+ | Id | +----+ | 2 | | 4 | +----+
这个题目题意是要求找到比前一天气温高的条目
# Write your MySQL query statement below select Id from Weather w1 where Temperature >(select Temperature from Weather w2 where to_days(w2.Date)=(to_days(w1.Date)-1))
这里用了子查询,针对外部查询的每一条记录,在子查询中找到前一天的气温 作为子查询的结果 最后与外部查询相比较,这样就能找出结果来
当然这里思路比较简单,我想出来的都是最简单 也是最容易理解的方案。
Write a SQL query to find all duplicate emails in a table named Person
.
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+
For example, your query should return the following for the above table:
+---------+ | Email | +---------+ | a@b.com | +---------+
Note: All emails are in lowercase.
找出有完全一样email的条目,
解题的思路在于group by 通过集聚函数count计算条目,当条目大于2则显示,当然这里不能使用where 因为在SQL里面where必须在group by之前,然后才能集聚
所以此处只能使用having来完成 group by集聚完成之后的任务
select Email from Person group by Email having count(*)>=2
181. Employees Earning More Than Their Managers
找出比他们经理还挣得多的员工
毫无疑问子查询duang起
select Name Employee from Employee a where Salary >(select Salary from Employee where Id=a.ManagerId)
175. Combine Two Tables
这题直接外连,不用讲思路了
select FirstName, LastName, City, State from Person left join Address on Address.PersonId=Person.PersonId