函数用法,over函数用法及界别

作者:新京葡娱乐场388官网

下文主要讲述:重复数据只获取一条的方法

  • distinct和group by 是一样的,查询去重,只能是全部重复的,也可以理解为针对单例,因为一行有一个字段不一样,他们就会认为这两行内容是不重复的。但是使用row_number()over这个函数就可以针对全部字段,完全重复还是部分重复都可以通过这个函数查找出来,因为它自身有分组的功能。以下就是具体代码:

    • 新京葡娱乐场388官网 1新京葡娱乐场388官网 2
    • 上面那个表就是在最后两行时候ID,NAME,BIRTHDAY三个字段值重复,AGE字段值不一样。
    • SELECT DISTINCT ID,NAME,BIRTHDAY FROM B查询结果就是:
    • 新京葡娱乐场388官网 3新京葡娱乐场388官网 4
    • 但是语句上加上AGE字段,SELECT DISTINCT ID,NAME,BIRTHDAY,AGE FROM B 结果就不一样了:
    • 新京葡娱乐场388官网 5新京葡娱乐场388官网 6
    • 会发现DISTINCT函数没有起作用,GROUP BY 函数和DISTINCT一样的,只是使用GROUP BY函数时候这样写就不会错:
      • SELECT ID,NAME,AGE,BIRTHDAY FROM B GROUP BY ID,NAME,AGE,BIRTHDAY;
    • 换一种写法:SELECT ID,NAME,AGE,BIRTHDAY FROM B GROUP BY ID;就会报错,意思就是SELECT后面的跟几个字段,GROUP BY 后面也要跟几个字段,要不然就会报错。
  • 新京葡娱乐场388官网,说一说ROW_NUMBER()OVER这个函数:

    • 删除一张表中重复数据,当你不知道那一个字段重复,或者你知道那一个字段重复,还有就是根据你的业务需求在用这个函数时候,因为这个函数在一定比较上还是有点麻烦,能不用就不用,但是呢这个函数内部有一个分组排序功能,也算是一种优化,像DISTINCT,不能随便用,成本高,效率低下,其实可以用group by 进行优化,其他的优化待续.......

    • ROW_NUMBER()OVER

      • DELETE FROM (SELECT ID,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY NAME)ROW_NUM FROM B)WHERE ROW_NUM>1;--这是删除表中全部重复部分重复的字段,根据业务需求。在你知道具体字段值重复时候直接delete from tablename where条件就可以删除。
    • 使用ROW_NUMBER()OVER函数查询时候去重怎么操作:

      • select * from (select id,name,age,birthday, row_number() over (partition by id order by id)rank from b)where rank =1;
    • 原来的表数据是这样的:

    • 新京葡娱乐场388官网 7新京葡娱乐场388官网 8

    • 执行上面代码后结果是这样的:

    • 新京葡娱乐场388官网 9新京葡娱乐场388官网 10

    • 使用row_number()over函数进行删除部分或者全部重复数据的代码是这样的:

      • delete from (select id,name,age,birthday, row_number() over (partition by id order by id)rank from b)where rank >1;
    • 执行之前数据是这样的:

    • 新京葡娱乐场388官网 11新京葡娱乐场388官网 12

    • 执行删除代码之后就是这样的:

    • 新京葡娱乐场388官网 13新京葡娱乐场388官网 14
      * 会这一个基本就行了,删除全部重复,部分重复,查询全部重复,查询部分重复均可用这一个。

    • 其实个人对这个函数理解是:在查询时候,进行partition by 分组,order by 排序,然后把查询出来的结果集取个别名,可以把它当成一张表进行条件查询,别名rank就是分组之后那一列列名,新生的表包含rank这一个列,此时,就可以对这张分组排序好的表进行查询,最后查出来的结果就是去重的,无论全部重复或者部分重复均可使用。删除语句的道理和这个查询一模一样。

 

row_number函数在数据库中的功能是为每一行 按照一定的规则生成一个编号,
我们常常利用这一属性,对表进行分页操作,下文我们将讲述采用 row_number函数删除表中重复数据行

(2)对查询结果进行排序:(无分组)

转自:

新京葡娱乐场388官网 15

/*建表*/
create table A(keyId int,info varchar(20))
go 
/*生成数据*/
insert into A(keyId,info)values
(1,'a'),(2,'b'),(3,'C'),(4,'d'),(5,'e'),
(1,'a'),(21,'b1'),(31,'C1'),(4,'d'),(51,'e'),
(1,'a'),(6,'b1'),(7,'C1'),(4,'d000'),(10,'e')
go


/*删除 keyId重复数据 中的另外几条*/
delete [A2] from 
(select row_number() over (Partition By keyId order by keyId) as keyId2,* from A ) as [A2]
where [A2].keyId2  >1 


/*
/*删除 所有列都重复数据 中的另外几条*/
delete [A2] from 
(select row_number() over (Partition By keyId,info order by keyId) as keyId2,* from A ) as [A2]
where [A2].keyId2  >1 
 */

/*展示删除后的数据*/

select * from A 
go

truncate table A 
drop table A 
go
SELECT empno,WORKDEPT,SALARY, Row_Number() OVER (partition by workdept ORDER BY salary desc) rank FROM employee   
--------------------------------------  
000010  A00 152750  1  
000110  A00 66500   2  
000120  A00 49250   3  
200010  A00 46500   4  
200120  A00 39250   5  
000020  B01 94250   1  
000030  C01 98250   1  
000130  C01 73800   2  

 

新京葡娱乐场388官网 16

partition by 用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组,它和聚合函数不同的地方在于它能够返回一个分组中的多条记录,而聚合函数一般只有一个反映统计值的记录。

select workdept,salary,dense_rank() over(partition by workdept order by salary) as dense_rank_order from emp order by workdept;  
------------------  
A00 39250   1  
A00 46500   2  
A00 49250   3  
A00 66500   4  
A00 152750  5  
B01 94250   1  
C01 68420   1  
C01 68420   1  
C01 73800   2  
C01 98250   3  
SELECT empno,WORKDEPT,SALARY, Row_Number() OVER (ORDER BY salary desc) rank FROM employee   
--------------------------------------  
000010  A00 152750  1  
000030  C01 98250   2  
000070  D21 96170   3  
000020  B01 94250   4  
000090  E11 89750   5  
000100  E21 86150   6  
000050  E01 80175   7  
000130  C01 73800   8  
000060  D11 72250   9  
select workdept,salary,rank() over(partition by workdept order by salary) as dense_rank_order from emp order by workdept;  
------------------  
A00 39250   1  
A00 46500   2  
A00 49250   3  
A00 66500   4  
A00 152750  5  
B01 94250   1  
C01 68420   1  
C01 68420   1  
C01 73800   3  

3、dense_rank() over()是连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 .

新京葡娱乐场388官网 17

使用ROW_NUMBER删除重复数据 
---假设表TAB中有a,b,c三列,可以使用下列语句删除a,b,c都相同的重复行。

新京葡娱乐场388官网 18

例如:employee,根据部门分组排序。

row_number() over()和rownum差不多,功能更强一点(可以在各个分组内从1开时排序).

     在使用 row_number() over()函数时候,over()里头的分组以及排序的执行晚于 where group by  order by 的执行。

新京葡娱乐场388官网 19

新京葡娱乐场388官网 20

新京葡娱乐场388官网 21

2、rank() over()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内).

1、row_number() over()排序功能:

(1) row_number() over()分组排序功能:

DELETE FROM (select year,QUARTER,RESULTS,row_number() over(partition by YEAR,QUARTER,RESULTS order by YEAR,QUARTER,RESULTS) AS ROW_NO FROM SALE )   
WHERE ROW_NO>1  

转载:

新京葡娱乐场388官网 22

 

本文由www.204.net发布,转载请注明来源

关键词: www.204.net