一、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 |+----+ 分析:意思就是在Weather表中,写一个SQL查询与前一天相比温度更高的日期对应的ID。 代码:
# Write your MySQL query statement belowSELECT w1.Id FROM Weather w1 JOIN Weather w2 ON TO_DAYS(w1.Date)=TO_DAYS(w2.Date)+1 And w1.Temperature>w2.Temperature;
其中,TO_DAYS(date) 给定一个日期date, 返回一个天数 (从年份0开始的天数 )
其他解法:
SELECT w1.Id FROM Weather w1, Weather w2 WHERE dateDiff(w1.Date,w2.Date) = 1 AND w1.Temperature > w2.Temperature;
其中,dateDiff() 函数返回两个日期之间的天数。
还有这样的方式:
date_add(w1.date,interval 1 day)=w2.date
w2.Date = DATE_SUB(w1.Date, INTERVAL 1 DAY)
二、Delete Duplicate Emails
Write a SQL query to delete all duplicate email entries in a table named Person
, keeping only unique emails based on its smallest Id.
+----+------------------+| Id | Email |+----+------------------+| 1 | john@example.com || 2 | bob@example.com || 3 | john@example.com |+----+------------------+Id is the primary key column for this table.
For example, after running your query, the above Person
table should have the following rows:
+----+------------------+| Id | Email |+----+------------------+| 1 | john@example.com || 2 | bob@example.com |+----+------------------+
分析:意思就是删除Email列中重复项所在的行,而且保留的不重复行Id更小。
代码:
# Write your MySQL query statement belowDELETE p1FROM Person p1, Person p2WHERE p1.Email = p2.Email AND p1.Id > p2.Id
其他解法:
DELETE FROM Person WHERE Id IN (SELECT P1.Id FROM Person AS P1, Person AS P2 WHERE P1.Id > P2.Id AND P1.Email = P2.Email);
报错:Runtime Error Message:You can't specify target table 'Person' for update in FROM clause
所以得注意:In mysql you must't update a table while using select clause , You can only do that step by step . However ,you can use a middle table as :
delete from Person where id not in( select t.id from ( select min(id) as id from Person group by email ) t )
或:
MySQL Don't allow referring delete target table in sub query, a workaround is use ( select * from Person ) to get a new table.
delete from Person where Id in ( select p1.Id from (select * from Person) p1, (select * from Person) p2 where p1.Email = p2.Email and p1.Id > p2.Id )
另外ps:刚开始也想过用"SELECT DISTINCT Email from Person" ,但是注意到:Delete and Distinct are completely different, while delete alters the table, distinct only selects distinct values and doesn't alter table. 所以这样是不可行的!
三、Customers Who Never Order
Suppose that a website contains two tables, the Customers
table and the Orders
table. Write a SQL query to find all customers who never order anything.
Table: Customers
.
+----+-------+| Id | Name |+----+-------+| 1 | Joe || 2 | Henry || 3 | Sam || 4 | Max |+----+-------+
Table: Orders
.
+----+------------+| Id | CustomerId |+----+------------+| 1 | 3 || 2 | 1 |+----+------------+
Using the above tables as example, return the following:
+-----------+| Customers |+-----------+| Henry || Max |+-----------+ 分析:题意为 假设一个网站包含两个表, 顾客表Customers和订单表Orders。编写一个SQL查询找出所有从未下过订单的顾客。 思路:使用NOT IN,NOT EXISTS,或者LEFT JOIN都是可以解决的。 解法一: NOT IN
# Write your MySQL query statement below SELECT NameFROM Customers C WHERE C.Id not in (select O.CustomerId from Orders O);
解法二:
NOT EXISTS
# Write your MySQL query statement belowSELECT Name FROM Customers c WHERE NOT EXISTS (SELECT CustomerId FROM Orders o WHERE o.CustomerId = c.id);
解法三:
SELECT C.NameFROM Customers AS C LEFT OUTER JOIN Orders AS OON C.Id = O.CustomerIdWHERE O.CustomerId IS NULL;