SQL去重复

谁践踏了优雅 2021-11-04 23:40 331阅读 0赞

select * from (  select * ,row_number() over(partition by DDID order by DDID desc) y  from LDZBSCJH where HTBH='V01461' )  as b  where y <>1

# SqlServer四种排序：ROW\_NUMBER()/RANK()/DENSE\_RANK()/ntile() over() #

先看一下最简单的sql语句以及最原始的效果图：

SELECT a.userName,a.userid FROM dbo.t_user a

![20180525162003502][]

【四种排序方式】

1.ROW\_NUMBER()排序

我们将这几行数据进行简单的排序，另给一个新的列RN

SELECT a.userName,a.userid,ROW_NUMBER()OVER	(order by a.userName) AS RN FROM dbo.t_user a

![20180525162112519][]

这时，我想针对名字这一列，将重复的数据以1开头排序，不存在重复的就是1

SELECT a.userName,a.userid,ROW_NUMBER()OVER (partition by a.userName ORDER by a.userName) AS RN FROM dbo.t_user a

![20180525162421999][]

2.RANK()排序

SELECT a.userName,a.userid,RANK()OVER (ORDER by a.userName) AS RN FROM dbo.t_user a

![2018052516273741][]

这时排序的应该为3的值，因为出现重复数据，所以也变成了2，但是最后的行数和最后编号的值是一样的。

3.DENSE\_RANK()排序

SELECT a.userName,a.userid,DENSE_RANK()over (ORDER by a.userName) AS RN FROM dbo.t_user a

![20180525163153330][]

保持了重复的数据RN值相同，但值得注意的是第4行数据变成了3，也就是说行数和最后的编号是发生了变化的。

SELECT a.userName,a.userid,NTILE(2)over (ORDER by a.userName) AS RN FROM dbo.t_user a

3.NTILE()排序

括号里面是几，就会分成几组

例如5行数据，分成2组，就会前面3个，后面2个，默认把多余的行分给前面；分成3组，第一组就会2个，第二组就会2个，第三组就会1个

SELECT a.userName,a.userid,NTILE(2)over (ORDER by a.userName) AS RN FROM dbo.t_user a

![20180525164138870][]

SELECT a.userName,a.userid,NTILE(3)over (ORDER by a.userName) AS RN FROM dbo.t_user a

![20180525164457790][]

# [ROW\_NUMBER() OVER函数的基本用法][ROW_NUMBER_ OVER] #

# ROW\_NUMBER() OVER函数的基本用法 #

# 语法：ROW\_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN) #

## [方法/步骤][Link 1] ##

## [简单的说row\_number()从1开始，为每一条分组记录返回一个数字，这里的ROW\_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。 ][Link 1] ##

[![1128165-20171212092441149-284986859.png][]][1128165-20171212092441149-284986859.png 1]

row\_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

实例：

初始化数据

create table employee (empid int ,deptid int ,salary decimal(10,2))insert into employee values(1,10,5500.00)insert into employee values(2,10,4500.00)insert into employee values(3,20,1900.00)insert into employee values(4,20,4800.00)insert into employee values(5,40,6500.00)insert into employee values(6,40,14500.00)insert into employee values(7,40,44500.00)insert into employee values(8,50,6500.00)insert into employee values(9,50,7500.00)

数据显示为

[![1128165-20171212092441259-1352521352.png][]][1128165-20171212092441259-1352521352.png 1]

需求：根据部门分组，显示每个部门的工资等级

预期结果：

[![1128165-20171212092441384-633943197.png][]][1128165-20171212092441384-633943197.png 1]

SQL脚本：

SELECT \*, Row\_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

转载于:https://www.cnblogs.com/netdbo/p/11081239.html

[20180525162003502]: /images/20211104/fb73ef8bba2d4e75aec5addbeaa46f0e.png
[20180525162112519]: /images/20211104/ac22259659994ff299a208cceeb4743f.png
[20180525162421999]: /images/20211104/0f1320d45a274555919bb65bfb860895.png
[2018052516273741]: /images/20211104/6d8bae69b3234220b75f4326572734cf.png
[20180525163153330]: /images/20211104/3afe819345b046f8984a176ecb701d19.png
[20180525164138870]: /images/20211104/d3db670125cd493284cee5e327a291a0.png
[20180525164457790]: /images/20211104/171c26bd5716476189e9384cfa43234e.png
[ROW_NUMBER_ OVER]: https://www.cnblogs.com/liuzhenlei/p/8026278.html
[Link 1]: https://jingyan.baidu.com/article/javascript:;
[1128165-20171212092441149-284986859.png]: /images/20211104/70d305d537024b2d865958e5ae1d9cf2.png
[1128165-20171212092441149-284986859.png 1]: http://jingyan.baidu.com/album/9989c74604a644f648ecfef3.html?picindex=1
[1128165-20171212092441259-1352521352.png]: /images/20211104/aad7474a3815427a838ee0d927df1802.png
[1128165-20171212092441259-1352521352.png 1]: http://jingyan.baidu.com/album/9989c74604a644f648ecfef3.html?picindex=2
[1128165-20171212092441384-633943197.png]: /images/20211104/d5ab1dd351914a9da9c789c203a66e91.png
[1128165-20171212092441384-633943197.png 1]: http://jingyan.baidu.com/album/9989c74604a644f648ecfef3.html?picindex=3