当区分查询时，Row_number()不起作用. [英] Row_number() not working when distinct the query.

问题描述

在区分查询时，Row_number()不起作用.

例如，

我的桌子上有400条记录.在该记录中，有一些重复项.

当我与众不同时，我只有4行. (正确)

但是，当我除了添加一个列而使用同一查询时，"Row_number()OVER(PARTITION BY RoleCode ORDER BY ID AS [Sl.No]")不起作用，它将显示所有400条记录.

请参阅我的查询(在Row_Number()之前)
-----------------------------------
从tab1中选择不同的ID，RoleCode，projcode，其中rolecode ="ALE"和projcode ="268''

输出
------
ID RoleCode ProjCode
1266 ALE 268
1347 ALE 268
1408 ALE 268
1420 ALE 268

使用Row_Number()之后的相同查询->不能正常工作并显示所有值
-------------------------------
从tab1中选择不同的ID，RoleCode，projcode，Row_number()OVER(按角色代码排序或按ID排序)AS [Sl.No]，其中rolecode ="ALE"和projcode ="268"


TranID RoleCode ProjCode Sl.No
1266 ALE 268 1
1266 ALE 268 2
1266 ALE 268 3
1266 ALE 268 4
1266 ALE 268 5
1266 ALE 268 6
1266 ALE 268 7
1266 ALE 268 8
1266 ALE 268 9
1266 ALE 268 10
1266 ALE 268 11
1266 ALE 268 12
1266 ALE 268 13
1266 ALE 268 14
1266 ALE 268 15
1266 ALE 268 16
--------
--------

受影响的400行

有什么问题，为什么在SQL查询中使用Row_number()时却无法正常工作...?

Hi,

Row_number() not working when distinct the query.

For Example,

I have 400 Records in my table. In that Records some duplicates are avaliable.

When i distinct i got only 4 Rows. (This is correct)

But, when i use the same query in addition to add one column "Row_number() OVER(PARTITION BY RoleCode ORDER BY ID AS [Sl.No]" distinct not working. it will show all 400 Records.

See my Query (Before Row_Number())
-----------------------------------
select distinct ID,RoleCode,projcode from tab1 where rolecode=''ALE'' and projcode=''268''

Output
------
ID RoleCode ProjCode
1266 ALE 268
1347 ALE 268
1408 ALE 268
1420 ALE 268

Same Query After using Row_Number() --> Distinct not working and shows all values
-------------------------------
select distinct ID,RoleCode,projcode,Row_number() OVER(PARTITION BY rolecode ORDER BY ID) AS [Sl.No] from tab1 where rolecode=''ALE'' and projcode=''268''


TranID RoleCode ProjCode Sl.No
1266 ALE 268 1
1266 ALE 268 2
1266 ALE 268 3
1266 ALE 268 4
1266 ALE 268 5
1266 ALE 268 6
1266 ALE 268 7
1266 ALE 268 8
1266 ALE 268 9
1266 ALE 268 10
1266 ALE 268 11
1266 ALE 268 12
1266 ALE 268 13
1266 ALE 268 14
1266 ALE 268 15
1266 ALE 268 16
---- --- --- --
---- --- --- --

400 Rows Affected

What is the problem and why distinct not working when using Row_number() in SQL Query...?

推荐答案

尝试以下一项:

Try this one :

select * , Row_number() OVER(PARTITION BY rolecode ORDER BY ID) AS [Sl.No] from
( 
   select distinct ID,RoleCode,projcode from tab1 where rolecode='ALE' and projcode='268'
) as s

这篇关于当区分查询时，Row_number()不起作用.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！