MySQL 抑制重复

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

一.需求

在表EMP中查找不同的职位，但不想看到有重复。其结果集应该是:

±----------+
| job |
±----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
±----------+

二.解决方案

2.1 distinct 方法

select distinct job 
 from  emp;

测试记录:

mysql> select distinct job
    ->  from  emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

2.2 group by 方法

select job
  from emp
group by job;

测试记录:

mysql> select job
    ->   from emp
    -> group by job;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+
5 rows in set (0.00 sec)

2.3 MySQL 8.0 窗口函数方法

select job
  from (
select job,
       row_number() over w as 'rn'
  from emp
window w (partiton by job order by job)
       ) x
where rn = 1;

测试记录:

mysql> select job
    ->   from (
    -> select job,
    ->        row_number() over w as 'rn'
    ->   from emp
    -> window w as (partition by job order by job)
    ->        ) x
    -> where rn = 1;
+-----------+
| job       |
+-----------+
| ANALYST   |
| CLERK     |
| MANAGER   |
| PRESIDENT |
| SALESMAN  |
+-----------+
5 rows in set (0.00 sec)