LeetCode--SQL（easy）（2）

问题：查找重复邮箱

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 |
+---------+

我的做法：速度201ms

select distinct Email
from Person
where Id not in (select min(a.Id)
                 from Person as a
                 group by a.Email);

标准做法：

select Email
from Person
group by Email
having count(Email) > 1;

select Email from
(
  select Email, count(Email) as num
  from Person
  group by Email
) as statistic
where num > 1
;

 第一种做法考察group by与having，第二种做法考察group by。做本题的关键是，要知道重复的email计数大于1，即利用count解决去重问题。 

问题：找出未下订单的客户姓名

Suppose that a website contains two tables, the Customers table and the Orderstable. 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       |
+-----------+

我的方法：（240ms）

# Write your MySQL query statement below
select Name as Customers
from Customers 
where Id not in (select CustomerId
                from Orders);

标准答案：

select customers.name as 'Customers'
from customers
where customers.id not in
(
    select customerid from orders
);

考点是 not in

 

问题：去除重复邮箱

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  |
+----+------------------+

Note:

Your output is the whole Person table after executing your sql. Use deletestatement.

本题我本想延续《挑选重复邮箱》的做法，但是行不通，下面给出标准做法

DELETE p1
FROM Person as p1, Person as p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id;

问题：温度提升

 

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) | RecordDate(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 |
+----+

我的方法:

SELECT w1.Id
FROM Weather as w1,Weather as w2
where w1.RecordDate= DATE_ADD(w2.RecordDate, interval 1 day)
AND w1.Temperature > w2.Temperature;

 标准答案：

SELECT
    weather.id AS 'Id'
FROM
    weather
        JOIN
    weather w ON DATEDIFF(weather.date, w.date) = 1
        AND weather.Temperature > w.Temperature
;

考点：时间函数的增加、减少和相减。增加日期：date_add(date,interval 1 day);减少日期：date_sub(date,interval 1,day);

日期相减：datediff(date1,date2)。

 

 问题：大国

There is a table World

+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million.

Write a SQL solution to output big countries' name, population and area.

For example, according to the above table, we should output:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

我的方法：

SELECT name,population,area
from World
where area >3000000 or population >25000000;

 标准答案除了我的解决方法，还有下面方法：

SELECT
    name, population, area
FROM
    world
WHERE
    area > 3000000

UNION

SELECT
    name, population, area
FROM
    world
WHERE
    population > 25000000
;

 考点：OR