代码之家  ›  专栏  ›  技术社区  ›  Ratha

从返回重复记录的sql组中选择id?

  •  1
  • Ratha  · 技术社区  · 5 年前

    CompanyId 列仅来自以下SQL;

    select CompanyId,
               row_number() over (partition by [GradeName] order by [TankNumber] ) rn
        from [Data_DB].[dbo].[Company]  where CompanyCode='ASAAA'
    

    在SQL中,我试图找出重复的记录,并从另一个表中根据

    就是,;

    delete from [[dbo].ObservationData 
    where CompanyId in (select CompanyId,
                   row_number() over (partition by [GradeName] order by [TankNumber] ) rn
            from [Data_DB].[dbo].[Company]  where CompanyCode='ASAAA')
    

    2 回复  |  直到 5 年前
        1
  •  3
  •   Ratha    5 年前

    假设您不在乎保留或删除哪个副本,您可以尝试在此处使用可删除CTE:

    WITH cte AS (
        SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [GradeName] ORDER BY [TankNumber]) rn
        FROM [Data_DB].[dbo].[Company]
        WHERE CompanyCode = 'ASAAA'
    )
    
    DELETE
    FROM cte
    WHERE rn > 1;
    

    此答案任意保留“第一个”副本,第一个被定义为具有最早行号的记录。

        2
  •  0
  •   Ratha    5 年前
    delete from [[dbo].ObservationData 
    where CompanyId in (select CompanyId from (select CompanyId,
                   row_number() over (partition by [GradeName] order by [TankNumber] ) rn
            from [Datat_DB].[dbo].[Company]  where CompanyCode='ASAAA') a where rn > 1 ;