196. 删除重复的电子邮箱

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, '[e-mail]'),
       (2, '[e-mail]'),
       (3, '[e-mail]');

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, '[e-mail]'),
       (2, '[e-mail]'),
       (3, '[e-mail]'),
       (4, '[e-mail]'),
       (5, '[e-mail]');

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