sql server中的行号（）函数。 [英] row number() function in sql server.

问题描述

您好，

Hello,

AS
BEGIN
WITH cte
  AS
  (
    SELECT row_number () OVER ( PARTITION BY
    BillofMaterialsId, MasterItemId, MemberItemId, MemberRevisionId  ORDER BY BillofMaterialsId DESC ) AS ROW,
    *
   FROM BOMDetails_Stg
  )
  DELETE CTE
  WHERE ROW > 1

此代码的作用实际上请解释一下。



提前谢谢。

What this code does actually please explain me.

Thanks in advance.

推荐答案

下面是解释

Below is the explanation

WITH cte
  AS
  (
    SELECT row_number () OVER ( PARTITION BY
    BillofMaterialsId, MasterItemId, MemberItemId, MemberRevisionId  ORDER BY BillofMaterialsId DESC ) AS ROW,
    *
   FROM BOMDetails_Stg
  )

上面的代码将对重复值进行排名。这意味着如果你有以下数据

BillofMaterialsId MasterItemId MemberItemId MemberRevisionId

AAA BBB CCC DDD

AAA BBB CCC DDD

EEE FFF GGG HHH



然后row_number（）函数将前两行排列为1,2和第三行为1.

所以如果我们想从上表中删除重复，那么我们需要删除rownumber为2的数据

Above code will rank duplicate values . It means if you have below data
BillofMaterialsId MasterItemId MemberItemId MemberRevisionId
AAA BBB CCC DDD
AAA BBB CCC DDD
EEE FFF GGG HHH

Then row_number () function will rank first two rows as 1 , 2 and third row as 1.
So if we want to remove duplicate from above table then we need to delete data which has rownumber as 2

DELETE CTE
 WHERE ROW > 1

如果第一个代码WITH函数将使用ROW_NUMBER创建CTE表如前面提到的 。此查询将从实际表中删除ROW_NUMBER大于1的数据。表示它将删除重复的行。

If the first code WITH function will create CTE table with ROW_NUMBER as mentioned earlier . This query will remove data from actual table where ROW_NUMBER is greater than 1. Means it will remove duplicate row.

这篇关于sql server中的行号（）函数。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！