distinct去重复:
当 distinct 作用在多个字段的时候,它只会将所有字段值都相同的记录“去重”掉。
关键字 distinct 只能放在 SQL 语句中所有字段的最前面才能起作用,如果放错位置,SQL 不会报错,但也不会起到任何效果。
二、row_number() over() 函数
函数 row_number() 用于给数据库表中的记录进行标号,在使用的时候,其后还跟着一个函数 over(),而函数 over()的作用是将表中的记录进行分组和排序。
1.语法:row_number() over(partition by COLUMN_NAME1 order by COLUMN_NAME2 DESC)
含义:将查询结果按照COLUMN_NAME1字段分区,然后每个分区里面的数据再按照COLUMN_NAME2的优先级顺序排序, 至于asc 和desc 就是升序和降序了,可以不写,默认是asc。
PARTITION BY:表示分组;ORDER BY:表示排序
例如:查询emp1表,按照ename字段分组,sal排序--
select deptno,ename, row_number() over(partition by ename order by sal) from emp1;
结果如下:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
1 7876 ADAMS CLERK 7788 1987/5/23 1320.00 20 1
2 7499 ALLEN SALESMAN 7698 1981/2/20 2400.00 300.00 30 1
3 7698 BLAKE MANAGER 7839 1981/5/1 5700.00 30 1
4 7782 CLARK MANAGER 7839 1981/6/9 4900.00 10 1
5 7902 FORD ANALYST 7566 1981/12/3 1140.00 20 1
6 7900 JAMES ww 222 1988/1/18 1000.00 1
7 7900 JAMES CLERK 7698 1981/12/3 1140.00 30 2
8 7566 JONES MANAGER 7839 1981/4/2 5950.00 20 1
9 7839 KING PRESIDENT 1981/11/17 4900.00 10 1
10 7654 MARTIN SALESMAN 7698 1981/9/28 1500.00 1400.00 30 1
11 7934 MILLER CLERK 7782 1982/1/23 1560.00 10 1
12 123 MILLER qq 7782 1988/1/14 1800.00 2
13 7788 SCOTT ANALYST 7566 1987/4/19 4900.00 20 1
14 7369 SMITH CLERK 7902 1980/12/17 960.00 20 1
15 7844 TURNER SALESMAN 7698 1981/9/8 2250.00 0.00 30 1
16 7521 WARD SALESMAN 7698 1981/2/22 1500.00 500.00 30 1
我们发现6和7、11和12的字段有重复的,那我们如何去重复只保留一条数据呢?
利用row_number() over()函数以 ENAME 分组,以 sal 排序,然后再取每组的第一个记录或许就可以实现“去重”的功能:
select * from
(select emp1.*, row_number() over(partition by ename order by sal) rn
from emp1)
where rn = 1;
解释:将row_number() over(partition by ename order by sal)定一个别名rn
结果如下:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO RN
1 7876 ADAMS CLERK 7788 1987/5/23 1320.00 20 1
2 7499 ALLEN SALESMAN 7698 1981/2/20 2400.00 300.00 30 1
3 7698 BLAKE MANAGER 7839 1981/5/1 5700.00 30 1
4 7782 CLARK MANAGER 7839 1981/6/9 4900.00 10 1
5 7902 FORD ANALYST 7566 1981/12/3 1140.00 20 1
6 7900 JAMES ww 222 1988/1/18 1000.00 1
7 7566 JONES MANAGER 7839 1981/4/2 5950.00 20 1
8 7839 KING PRESIDENT 1981/11/17 4900.00 10 1
9 7654 MARTIN SALESMAN 7698 1981/9/28 1500.00 1400.00 30 1
10 7934 MILLER CLERK 7782 1982/1/23 1560.00 10 1
11 7788 SCOTT ANALYST 7566 1987/4/19 4900.00 20 1
12 7369 SMITH CLERK 7902 1980/12/17 960.00 20 1
13 7844 TURNER SALESMAN 7698 1981/9/8 2250.00 0.00 30 1
14 7521 WARD SALESMAN 7698 1981/2/22 1500.00 500.00 30 1
结语:
在使用函数 row_number() over() 的时候,是按先分组排序,再取出每组的第一条记录来进行“去重”。还可以通过不同的限制条件来进行“去重”,具体如何实现,就需要根据实际情况了。
三、利用rowid伪列
select count(*) from 表名 where rowid in (select max(rowid) from 表名 group By 字段)
不过这种方法遇到数据量多的情况,效率是非常低下的