备注:测试数据库版本为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)