lyfz_repos/经典版源码/document/数据库维护脚本/跨年转数据/dindansp过滤掉完全重复和不完全重复记录.sql

--0.备份当前db的dindansp表到dindansp_back
select [id],[spid],[shuliang],[kind],[price]
           ,[name],[no],[status1],[status2],[status3]
           ,[status4],[date1],[date2],[date3],[date4],[name1]
           ,[name2],[name3],[name4],[hqdate],[hqtime],[urgent]
           ,[taketime],[no2],[status5],[status6],[date5],[date6],[name5]
           ,[name6],[growthtxname],[ps],[zs],[ren1],[ren2],[hqstatus],[bz] 
into [db].[dbo].[dindansp_back] from [db].[dbo].[dindansp]
go

--1.将2014和db的dindnasp表的全部记录插入到#tempall中;
select [id],[spid],[shuliang],[kind],[price]
           ,[name],[no],[status1],[status2],[status3]
           ,[status4],[date1],[date2],[date3],[date4],[name1]
           ,[name2],[name3],[name4],[hqdate],[hqtime],[urgent]
           ,[taketime],[no2],[status5],[status6],[date5],[date6],[name5]
           ,[name6],[growthtxname],[ps],[zs],[ren1],[ren2],[hqstatus],[bz] 
into #tempall  from [db].[dbo].[dindansp]
go

insert into #tempall
select [id],[spid],[shuliang],[kind],[price]
           ,[name],[no],[status1],[status2],[status3]
           ,[status4],[date1],[date2],[date3],[date4],[name1]
           ,[name2],[name3],[name4],[hqdate],[hqtime],[urgent]
           ,[taketime],[no2],[status5],[status6],[date5],[date6],[name5]
           ,[name6],[growthtxname],[ps],[zs],[ren1],[ren2],[hqstatus],[bz] 
		   from [2014].[dbo].[dindansp]
go

--2.过滤掉#tempall中完全重复的记录到#tempdis中;
select distinct * into #tempdis from #tempall
go

drop table #tempall
go

--3.获取指定字段的不重复记录,过滤掉不完全重复的记录;
select identity(int,1,1) as autoid, * into #temp1 from #tempdis
select min(autoid) as autoid into #temp2 from #temp1 group by [id],[spid],[name],[no]
select [id],[spid],[shuliang],[kind],[price]
           ,[name],[no],[status1],[status2],[status3]
           ,[status4],[date1],[date2],[date3],[date4],[name1]
           ,[name2],[name3],[name4],[hqdate],[hqtime],[urgent]
           ,[taketime],[no2],[status5],[status6],[date5],[date6],[name5]
           ,[name6],[growthtxname],[ps],[zs],[ren1],[ren2],[hqstatus],[bz] 
into [db].[dbo].[dindansp#] from #temp1 where autoid in(select autoid from #temp2) order by id
go

--4.将过滤好的记录重新插入原表;
drop table [db].[dbo].[dindansp]
go

use [db]
CREATE TABLE [dbo].[dindansp](
	[id] [nvarchar](50) NULL,
	[spid] [nvarchar](50) NULL,
	[shuliang] [nvarchar](120) NULL,
	[kind] [nvarchar](10) NULL,
	[price] [nvarchar](50) NULL,
	[name] [nvarchar](50) NULL,
	[no] [nvarchar](max) NULL,
	[status1] [nvarchar](50) NULL,
	[status2] [nvarchar](50) NULL,
	[status3] [nvarchar](50) NULL,
	[status4] [nvarchar](50) NULL,
	[autoid] [int] IDENTITY(1,1) NOT NULL,
	[date1] [nvarchar](50) NULL,
	[date2] [nvarchar](50) NULL,
	[date3] [nvarchar](50) NULL,
	[date4] [nvarchar](50) NULL,
	[name1] [nvarchar](50) NULL,
	[name2] [nvarchar](50) NULL,
	[name3] [nvarchar](50) NULL,
	[name4] [nvarchar](50) NULL,
	[hqdate] [nvarchar](50) NULL,
	[hqtime] [nvarchar](50) NULL,
	[urgent] [nvarchar](50) NULL,
	[taketime] [nvarchar](50) NULL,
	[no2] [nvarchar](max) NULL,
	[growthtxname] [nvarchar](50) NULL,
	[status5] [nvarchar](50) NULL,
	[status6] [nvarchar](50) NULL,
	[date5] [nvarchar](50) NULL,
	[date6] [nvarchar](50) NULL,
	[name5] [nvarchar](50) NULL,
	[name6] [nvarchar](50) NULL,
	[ps] [nvarchar](50) NULL,
	[zs] [nvarchar](50) NULL,
	[ren1] [nvarchar](120) NULL,
	[ren2] [nvarchar](120) NULL,
	[hqstatus] [nvarchar](50) NULL,
	[bz] [nvarchar](200) NULL
) ON [PRIMARY]
go

insert into [db].[dbo].[dindansp]([id],[spid],[shuliang],[kind],[price]
           ,[name],[no],[status1],[status2],[status3]
           ,[status4],[date1],[date2],[date3],[date4],[name1]
           ,[name2],[name3],[name4],[hqdate],[hqtime],[urgent]
           ,[taketime],[no2],[status5],[status6],[date5],[date6],[name5]
           ,[name6],[growthtxname],[ps],[zs],[ren1],[ren2],[hqstatus],[bz] ) 
select [id],[spid],[shuliang],[kind],[price]
           ,[name],[no],[status1],[status2],[status3]
           ,[status4],[date1],[date2],[date3],[date4],[name1]
           ,[name2],[name3],[name4],[hqdate],[hqtime],[urgent]
           ,[taketime],[no2],[status5],[status6],[date5],[date6],[name5]
           ,[name6],[growthtxname],[ps],[zs],[ren1],[ren2],[hqstatus],[bz]  from [db].[dbo].[dindansp#] 
go

drop table #tempdis 
drop table #temp1 
drop table #temp2 
drop table [db].[dbo].[dindansp#] 
go
--5.结束