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

--0.备份当前db的dindanbukuan表到dindanbukuan_back中;
select [id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[autoid],[jdd],[financecheck],[discount],[time]
into [db].[dbo].[dindanbukuan_back] from [db].[dbo].[dindanbukuan]
go

--1.将2014和db的dindanbukuan表全部记录插入到#tempall表中;
select [id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[jdd],[financecheck],[discount],[time]
into #tempall from [db].[dbo].[dindanbukuan]
go

insert into #tempall
select [id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[jdd],[financecheck],[discount],[time]
from [2014].[dbo].[dindanbukuan]
go

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

--3.获取指定字段的不重复记录,过滤掉不完全重复的记录;
select identity(int,1,1) as autoid, * into #temp1 from #tempdis
select min(autoid) as autoid into #temp2 from #temp1 group by id,money,ren,date,bz,kind,ren2,item,paytype,jdd,time
select [id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[autoid],[jdd],[financecheck],[discount],[time]
into [db].[dbo].[dindanbukuan#] from #temp1 where autoid in(select autoid from #temp2) order by id
go

--4.将过滤好的记录重新插入原表;
drop table #tempdis
drop table #temp1
drop table #temp2
drop table [db].[dbo].[dindanbukuan]

use [db]
CREATE TABLE [dbo].[dindanbukuan](
	[autoid] [int] IDENTITY(1,1) NOT NULL,
	[id] [nvarchar](50) NULL,
	[money] [nvarchar](50) NULL,
	[ren] [nvarchar](50) NULL,
	[date] [nvarchar](50) NULL,
	[bz] [nvarchar](max) NULL,
	[kind] [nvarchar](50) NULL,
	[ren2] [nvarchar](50) NULL,
	[item] [nvarchar](max) NULL,
	[paytype] [nvarchar](50) NULL,
	[jdd] [nvarchar](50) NULL,
	[financecheck] [nvarchar](10) NULL,
	[discount] [nvarchar](50) NULL,
	[time] [nvarchar](50) NULL
) ON [PRIMARY]

insert into [db].[dbo].[dindanbukuan]([id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[jdd],[financecheck],[discount],[time]) 
select [id],[money],[ren],[date],[bz],[kind],[ren2],[item],[paytype],[jdd],[financecheck],[discount],[time] 
from [db].[dbo].[dindanbukuan#]

drop table [db].[dbo].[dindanbukuan#]
--5.结束;