mssql 过滤重复记录，取第一笔记录

 

 

with TS as( 
                SELECT ROW_NUMBER() over (PARTITION By code order by code) as rowid,* FROM StateInfos   
)
select        [Hexcode]
           ,[DisplayName]
           ,[Code]
           ,[OriginalCode]
           ,[IsPlaned]
           ,[Type]
           ,[IsStatic]
           ,[StateInfoGroupId] from TS where ts.rowid = 1

 

以下语句：过滤掉 StateInfos表重复数据，并且不插入Code重复的数据

with TS as( 
                SELECT ROW_NUMBER() over (PARTITION By code order by code) as rowid,* FROM StateInfos   
)
                                 
                                        INSERT INTO [dbo].[StateInfos2]
           ([Hexcode]
           ,[DisplayName]
           ,[Code]
           ,[OriginalCode]
           ,[IsPlaned]
           ,[Type]
           ,[IsStatic]
           ,[StateInfoGroupId])
                                    select [Hexcode]
           ,[DisplayName]
           ,[Code]
           ,[OriginalCode]
           ,[IsPlaned]
           ,[Type]
           ,[IsStatic]
           ,[StateInfoGroupId] from TS where ts.rowid = 1 and not exists(select 1 from StateInfos2 where StateInfos2.Code = TS.Code)