MSQL——力扣SQL语句练习

1.【查找重复邮箱】编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

SELECT p1.*
FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email

Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person表应返回以下几行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

答案：delete from Person where Id not in(select Id from ( select min(Id) Id,Email from Person group by Email)t)

官方答案方法：使用 DELETE 和 WHERE 子句

算法

我们可以使用以下代码，将此表与它自身在电子邮箱列中连接起来

SELECT p1.*
FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email
;

然后我们需要找到其他记录中具有相同电子邮件地址的更大 ID。所以我们可以像这样给 WHERE 子句添加一个新的条件

SELECT p1.*
FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id
;

因为我们已经得到了要删除的记录，所以我们最终可以将该语句更改为 DELETE

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

 

 2.【大的国】

 答案：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;

3.【查找重复电子邮箱】

答案：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;

4. 【有趣的电影】

答案： select * from cinema where mod(id, 2) = 1 and description != 'boring' order by rating DESC;

取余是用函数mod(numer1,number2)，其返回的值为其余数值

如：mod(id,2) = 1 返回id号是奇数的id

 5.【组合两个表】

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

FirstName, LastName, City, State

答案：select FirstName, LastName, City, State from Person left join Address on Person.PersonId = Address.PersonId;

6.【交换工资】

答案： UPDATE salary SET sex = CASE sex WHEN 'm' THEN 'f' ELSE 'm' END;

7.【超过经理收入的员工】

答案：select p2.name Employee from employee p1 inner join employee p2 on p1.Id = p2.ManagerId and p2.Salary > p1.Salary and p2.ManagerId IS NOT NULL

答案：select a.Name as Employee from Employee a where a.Salary > (select b.Salary from Employee b where b.Id = a.ManagerId)

8. 【从不订购的客户】

答案：select Name as Customers from Customers c left join Orders o on c.Id=o.CustomerId where o.CustomerId is null

答案：select Name as Customers from Customers as c1 where c1.Id not in ( select distinct CustomerId
from Orders)

9. 【换座位】

答案：
select (case
    when mod(id,2) =1 and id = sm.ma then id
    when mod(id,2) =1 and id <> sm.ma then id+1
    else id-1
   end) id,
   student
from seat,(select max(id) as ma from seat) sm order by id 

10.【上升的温度】

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

DATEDIFF() 函数返回两个日期之间的天数。语法DATEDIFF(date1,date2)

11.【超过5名学生的课】

 

答案：select class from courses group by class having count(distinct(student))>4

12.【第二高的薪水】

答案：SELECT(SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1) AS SecondHighestSalary

答案：SELECT  IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC  LIMIT 1 OFFSET 1), NULL) AS SecondHighestSalary

 //待续*********