| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463 |
- --0.备份当前db的dindan表到dindan_back中;
- select * into [db].[dbo].[dindan_back] from [db].[dbo].[dindan]
- go
- --1.将2014和db的dindan表全部记录插入到#tempall表中;
- select * into #tempall from [db].[dbo].[dindan]
- go
- insert #tempall
- ([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])
- 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]
- from [2014].[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#]
- go
- --5.结束;
|
