整理数据库
选出重复行,注意<或者>可以留下最大值或者最小值,<>删除全部重复值
select * from newbindtable as b1
where b1.serviceuserName in(select serviceusername from newbindtable
where serviceusername=b1.serviceusername and bindid<b1.bindid)
where b1.serviceuserName in(select serviceusername from newbindtable
where serviceusername=b1.serviceusername and bindid<b1.bindid)
删除重复行
delete newbindtable from newbindtable as b1
where b1.serviceuserName in(select serviceusername from newbindtable
where serviceusername=b1.serviceusername and bindid<b1.bindid)
where b1.serviceuserName in(select serviceusername from newbindtable
where serviceusername=b1.serviceusername and bindid<b1.bindid)
删除完全重复行
select distinct * into #temp from newsipidtable
drop table newsipidtable
select * into newsipidtable from #temp
drop table #temp
drop table newsipidtable
select * into newsipidtable from #temp
drop table #temp
用内连接选出数据
select b.serviceusername,b.password,substring(b.serviceusername,0,charindex('@',b.serviceusername)) as passport, s.sipid,s.sippwd from newbindtable as b
inner join newsipidtable as s
on b.serviceusername=s.jid
inner join newsipidtable as s
on b.serviceusername=s.jid
把选出的数据存到表中:
select * into new5
from
(select b.serviceusername,b.password,substring(b.serviceusername,0,charindex('@',b.serviceusername)) as passport, s.sipid,s.sippwd from newbindtable as b
inner join newsipidtable as s
on b.serviceusername=s.jid) as f
from
(select b.serviceusername,b.password,substring(b.serviceusername,0,charindex('@',b.serviceusername)) as passport, s.sipid,s.sippwd from newbindtable as b
inner join newsipidtable as s
on b.serviceusername=s.jid) as f
用正则:
1、把选择好的数据拷贝到UtraEdit
2、在替换中写正则如:^(.*)\t(.*)\t(.*)\t(.*)\t(.*)$,不同与.net中的语法
3、被替换文本:
insert into bind(passport,password,email,question,answer,bindvalue)values('\1','\2','','','','<?xml version="1.0"?> <ServiceInfos xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <ServieInfo> <ServiceName>Sip</ServiceName> <Username>\4</Username> <Password>\5</Password> </ServieInfo> <ServieInfo> <ServiceName>VirtualBank</ServiceName> <Username>\3</Username> <Password>\2</Password> </ServieInfo> <ServieInfo> <ServiceName>Blog</ServiceName> <Username>\3</Username> <Password>\2</Password> </ServieInfo> <ServieInfo> <ServiceName>Ex</ServiceName> <Username>\5</Username> <Password>\2</Password> </ServieInfo> <ServieInfo> <ServiceName>Jabber</ServiceName> <Username>\1</Username> <Password>\2</Password> </ServieInfo> </ServiceInfos>')
4、生成的sql语句
insert into bind(passport,password,email,question,answer,bindvalue)values('0@im.chinabubble.com','muxu323','','','','<?xml version="1.0"?> <ServiceInfos xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <ServieInfo> <ServiceName>Sip</ServiceName> <Username>1000000080</Username> <Password>267153</Password> </ServieInfo> <ServieInfo> <ServiceName>VirtualBank</ServiceName> <Username>0</Username> <Password>muxu323</Password> </ServieInfo> <ServieInfo> <ServiceName>Blog</ServiceName> <Username>0</Username> <Password>muxu323</Password> </ServieInfo> <ServieInfo> <ServiceName>Ex</ServiceName> <Username>267153</Username> <Password>muxu323</Password> </ServieInfo> <ServieInfo> <ServiceName>Jabber</ServiceName> <Username>0@im.chinabubble.com</Username> <Password>muxu323</Password> </ServieInfo> </ServiceInfos>')