| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173 |
- --0.备份当前db的dindan表到dindan_back中;
- select * into [db].[dbo].[dindan_back] from [db].[dbo].[dindan]
- go
- --1.将db的dindan表全部记录插入到#tempall表中;
- select * into #tempall from [db].[dbo].[dindan]
- 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]
- select [id]
- ,[money1]
- ,[money2]
- ,[money3]
- ,[name1]
- ,[name2]
- ,[taoxiid]
- ,[taoxiname]
- ,[taoxijiage]
- ,[taoxizs]
- ,[time1]
- ,[time2]
- ,[time3]
- ,[bm]
- ,[ren]
- ,[discount]
- ,[payed1]
- ,[payed2]
- ,[payed3]
- ,[payed4]
- ,[payed5]
- ,[time4]
- ,[time5]
- ,[takezs]
- ,[choosezs]
- ,[status]
- ,[clothescount2]
- ,[outside2]
- ,[style]
- ,[payed3time]
- ,[payed4ren]
- ,[payed4time]
- ,[status2]
- ,[status3]
- ,[waiter1]
- ,[waiter2]
- ,[waiter3]
- ,[waiter4]
- ,[bz]
- ,[taketime]
- ,[clothescount]
- ,[outside]
- ,[bz2]
- ,[bz3]
- ,[urgent]
- ,[status4]
- ,[status5]
- ,[waiter5]
- ,[waiter6]
- ,[pinyin1]
- ,[pinyin2]
- ,[waiter7]
- ,[status6]
- ,[phone1]
- ,[phone2]
- ,[status7]
- ,[memberno]
- ,[waiter8]
- ,[time6]
- ,[status8]
- ,[bruncount]
- ,[tichenren1]
- ,[tichenren2]
- ,[tichenren3]
- ,[tichenren4]
- ,[tichenren5]
- ,[send1]
- ,[send2]
- ,[send3]
- ,[datetime4]
- ,[datetime5]
- ,[datetime6]
- ,[waiter12]
- ,[waiter13]
- ,[waiter14]
- ,[waiter22]
- ,[waiter23]
- ,[waiter24]
- ,[waiter1rate]
- ,[waiter12rate]
- ,[waiter13rate]
- ,[waiter14rate]
- ,[waiter2rate]
- ,[waiter22rate]
- ,[waiter23rate]
- ,[waiter24rate]
- ,[ren2]
- ,[renrate]
- ,[ren2rate]
- ,[delphotos]
- ,[delphotos2]
- ,[txtype]
- ,[growthtxselname]
- ,[designno]
- ,[designreplaceno]
- ,[size]
- ,[dindantype]
- ,[contractno1]
- ,[contractno2]
- ,[cfno]
- ,[calldate]
- ,[from]
- ,[reason]
- ,[time7]
- ,[time8]
- ,[time9]
- ,[time10]
- ,[authorize]
- ,[discount2]
- ,[authorize2]
- ,[bz4]
- ,[satisfaction1]
- ,[satisfaction2]
- ,[satisfaction3]
- ,[satisfaction4]
- ,[satisfaction5]
- ,[satisfaction6]
- ,[satisfaction7]
- ,[bz5]
- ,[send4]
- ,[send5]
- ,[waiter9]
- ,[curno]
- ,[xplrr]
- ,[xplrtime]
- ,[satisfaction8]
- ,[VisitPeople1]
- ,[VisitPeople2]
- ,[VisitPeople3]
- ,[VisitPeople4]
- ,[VisitPeople5]
- ,[VisitPeople6]
- ,[VisitPeople7]
- ,[VisitPeople8]
- ,[VisitTime1]
- ,[VisitTime2]
- ,[VisitTime3]
- ,[VisitTime4]
- ,[VisitTime5]
- ,[VisitTime6]
- ,[VisitTime7]
- ,[VisitTime8]
- into [db].[dbo].[dindan#] from #temp1 where autoid in(select autoid from #temp2) order by id
- go
- --4.将过滤好的记录重新插入原表;
- drop table [db].[dbo].[dindan]
- select * into [db].[dbo].[dindan] from [db].[dbo].[dindan#]
- go
- drop table #tempdis
- drop table #temp1
- drop table #temp2
- drop table [db].[dbo].[dindan#]
- --5.结束;
|
