CREATE DATABASE IF NOT EXISTS sql_practice; USE sql_practice;

182. 查找重复的电子邮箱

https://leetcode-cn.com/problems/duplicate-emails/ 

DROP TABLE IF EXISTS `Person`; CREATE TABLE `Person` ( `Id` bigint(20) NOT NULL AUTO_INCREMENT, `Email` varchar(100) NOT NULL COMMENT '电子邮箱', PRIMARY KEY (`Id`) ); insert into `Person` (Id, Email) values (1, 'a@b.com'), (2, 'c@d.com'), (3, 'a@b.com'); 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 ;

196. 删除重复的电子邮箱

DROP TABLE IF EXISTS `Person`; CREATE TABLE `Person` ( `Id` bigint(20) NOT NULL AUTO_INCREMENT, `Email` varchar(100) NOT NULL COMMENT '电子邮箱', PRIMARY KEY (`Id`) ); insert into `Person` (Id, Email) values (1, 'john@example.com'), (2, 'bob@example.com'), (3, 'john@example.com'), (4, 'john@example.com'), (5, 'john@example.com');

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

 

DELETE p1 FROM Person p1 INNER JOIN Person p2 ON p1.Email = p2.Email AND p1.Id > p2.Id

不准用组函数（MAX），取得最高薪水（给出两种解决方案）：https://yuanyu.blog.csdn.net/article/details/106574112#t9