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

/*

申明：此脚本禁用，因为[singleincomemoney]与[singleincome]的id必须相同的,即singleincome的id相当于是[singleincomemoney]的外键作用

*/

--0.备份当前db的singleincome表到singleincome_back
select [name]
       ,[money]
       ,[dat]
       ,[bz]
       ,[renyuan1]
       ,[renyuan2]
       ,[renyuan3]
       ,[sale2type]
       ,[paytype]
       ,[phone]
       ,[payed]
       ,[balance]
       ,[pinyin]
       ,[financecheck]
       ,[count]
       ,[time]
into [db].[dbo].[singleincome_back] from [db].[dbo].[singleincome]
go

--1.将2014和db的dindnasp3表的全部记录插入到#tempall中;
select [name]
       ,[money]
       ,[dat]
       ,[bz]
       ,[renyuan1]
       ,[renyuan2]
       ,[renyuan3]
       ,[sale2type]
       ,[paytype]
       ,[phone]
       ,[payed]
       ,[balance]
       ,[pinyin]
       ,[financecheck]
       ,[count]
       ,[time]
into #tempall  from [db].[dbo].[singleincome]
go

insert into #tempall
select [name]
       ,[money]
       ,[dat]
       ,[bz]
       ,[renyuan1]
       ,[renyuan2]
       ,[renyuan3]
       ,[sale2type]
       ,[paytype]
       ,[phone]
       ,[payed]
       ,[balance]
       ,[pinyin]
       ,[financecheck]
       ,[count]
       ,[time] from [2014].[dbo].[singleincome]
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 [name],[money],[dat],[bz],[renyuan1],[renyuan2],[renyuan3]
           ,[sale2type],[paytype],[phone],[payed],[balance],[pinyin],[financecheck],[count],[time]
select [name]
           ,[money]
           ,[dat]
           ,[bz]
           ,[renyuan1]
           ,[renyuan2]
           ,[renyuan3]
           ,[sale2type]
           ,[paytype]
           ,[phone]
           ,[payed]
           ,[balance]
           ,[pinyin]
           ,[financecheck]
           ,[count]
           ,[time]
into [db].[dbo].[singleincome#] from #temp1 where autoid in(select autoid from #temp2) order by [name]
go

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

use db
CREATE TABLE [dbo].[singleincome](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NULL,
	[money] [nvarchar](50) NULL,
	[dat] [nvarchar](50) NULL,
	[bz] [nvarchar](max) NULL,
	[renyuan1] [nvarchar](50) NULL,
	[renyuan2] [nvarchar](50) NULL,
	[renyuan3] [nvarchar](50) NULL,
	[sale2type] [nvarchar](50) NULL,
	[paytype] [nvarchar](50) NULL,
	[phone] [nvarchar](50) NULL,
	[payed] [nvarchar](50) NULL,
	[balance] [nvarchar](50) NULL,
	[pinyin] [nvarchar](50) NULL,
	[financecheck] [nvarchar](10) NULL,
	[count] [nvarchar](50) NULL,
	[time] [nvarchar](50) NULL
) ON [PRIMARY]
go

insert into [db].[dbo].[singleincome]([name]
           ,[money]
           ,[dat]
           ,[bz]
           ,[renyuan1]
           ,[renyuan2]
           ,[renyuan3]
           ,[sale2type]
           ,[paytype]
           ,[phone]
           ,[payed]
           ,[balance]
           ,[pinyin]
           ,[financecheck]
           ,[count]
           ,[time]) 
select [name]
           ,[money]
           ,[dat]
           ,[bz]
           ,[renyuan1]
           ,[renyuan2]
           ,[renyuan3]
           ,[sale2type]
           ,[paytype]
           ,[phone]
           ,[payed]
           ,[balance]
           ,[pinyin]
           ,[financecheck]
           ,[count]
           ,[time] from [db].[dbo].[singleincome#] 
go

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