SQL学习3.1-min()函数的应用

题目描述：

删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

### 正向思维：找出所有需要删除的记录的id

#### step1:找出所有出现的频次>1 emp_no 及最小id

 

1 2	SELECT MIN(id) AS id,emp_no FROM titles_test GROUP BY emp_no HAVING COUNT(emp_no) > 1

+------+--------+
|    1 |  10001 |
|    2 |  10002 |
|    3 |  10003 |
+------+--------+

#### step2:联合titles_test 找出所有 需要删除的id

 

1 2 3 4

SELECT a.id FROM titles_test a ,     (SELECT MIN(id) AS id,emp_no FROM titles_test         GROUP BY emp_no HAVING COUNT(emp_no) > 1) AS b WHERE a.emp_no = b.emp_no AND a.id > b.id

+----+
|  5 |
|  6 |
|  7 |
+----+

#### step3:根据id删除数据

 

1 2 3 4 5 6 7

DELETE FROM titles_test WHERE id IN (     SELECT id FROM(                 SELECT a.id FROM titles_test a ,                    (SELECT MIN(id) AS id,emp_no FROM titles_test                     GROUP BY emp_no HAVING COUNT(emp_no) > 1) AS b                 WHERE a.emp_no = b.emp_no AND a.id > b.id )t )

### 逆向思维：找出所有emp_no组中最小的那些id

#### step1: 找出所有emp_no组中最小的那些id

 

1	SELECT MIN(id) FROM titles_test GROUP BY emp_no

#### step2: 根据id删除数据

 

1 2	DELETE FROM titles_test WHERE id NOT IN (SELECT MIN(id) FROM titles_test GROUP BY emp_no)