【SQL】使用ROW_NUMBER() OVER 实现DISTINCT功能

准备数据如下:

SELECT [Id],[Name]FROM [ElvinTest].[dbo].[Employee]

===========================================

Id          Name

---------------------

1           熊战士

2           熊战士

3           熊战士

4           Obama

5           Matin

6           Matin

7           Matin

8           Matin

增加 ROW_NUMBER()OVER

SELECT [Id]

      ,[Name]    

      ,ROW_NUMBER()OVER(ORDERBY name) as RowNum

FROM [ElvinTest].dbo.[Employee]

===============================================

Id         Name   RowNum

---------------------------------

5          Matin     1

6          Matin     2

7          Matin     3

8          Matin     4

4          Obama  5

1          熊战士   6

2          熊战士   7

3          熊战士   8

分组进行ROW_NUMBER()OVER

SELECT [Id]

      ,[Name]    

      ,ROW_NUMBER()OVER(PARTITIONBY name ORDERBY name)as RowNum

FROM [ElvinTest].dbo.[Employee]

========================================================================

Id          Name       RowNum

------------------------------------------

5           Matin         1

6           Matin         2

7           Matin         3

8           Matin         4

4           Obama      1

1           熊战士       1

2           熊战士       2

3           熊战士       3

去除重复:

SELECT [Id],[Name]FROM

(

      SELECT[Id]

             ,[Name]    

             ,ROW_NUMBER()OVER(PARTITIONBY name  ORDER BY name) as RowNum

      FROM[ElvinTest].dbo.[Employee]

) T

WHERE T.RowNum= 1

====================================================================

Id          Name

-----------------

5           Matin

4           Obama

1           熊战士