SQL 2008,有表TB,现有语句流程:原表中改列名,去除某列的特定字符,插入若干新列并赋值,建立视图。原操作要五步,求优化为一步操作,谢谢!
oiph 2012-01-09 04:39:35 如题,目前我手里的主表是TB,另外要代入的三张表名:bmsz,bmdh,bmgs。这四个表都在数据库xbbyq中。
目前我能做到基本的功能实现,只是步骤太多。要分五步才能做完,求达人指点,想通过优化,只运行一次语句,就能达到现有视图(即第五步)的效果,谢谢!
我目前的语句如下:
1
改列名
sp_rename 'xbbyq.dbo.tb.用户号码通话所在地(中文)','thdzw','column'
2 对方号码去除17951、12593、12583开头的
update tb set 对方号码 = replace(replace( replace(对方号码,'17951',''),'12593','') ,'12583','')
where 对方号码 like '17951%' or 对方号码 like '12593%'or 对方号码 like '12583%'
3 插入新列并赋值(其中新列 renp、dip、cmsj尚未赋值)
alter table tb add jzh4 as substring(小区编号,1,4),dh07 as substring(对方号码,1,7),
sjdd as 呼叫日期+' '+substring(呼叫时间,1,2)+' '+用户号码通话所在地+' '+substring(小区编号,1,4),renp int,
dip int,cmsj VARCHAR(50),cgi as substring(小区全球识别码,8,11) ,
bgi as 用户号码通话所在地+'-'+ substring(小区编号,1,4)
4 空列赋值
update tb
set renp=
(select count(tt.对方号码)
from tb tt where tt.对方号码 =tb.对方号码 group by tt.对方号码)
update tb
set dip=
(select count(tt.bgi)
from tb tt where tt.bgi =tb.bgi group by tt.bgi)
update tb set cmsj =
(select min(convert(varchar(10),呼叫日期,120)) from tb where 对方号码 = t.对方号码) +
'_' +
(select max(convert(varchar(10),呼叫日期,120)) from tb where 对方号码 = t.对方号码)
from tb t
5 建立视图
create view tbA as
select a.hjrq,a.hjsj,a.thd,a.thdzw,a.jzm,a.dip,a.yhhm,a.txfs,a.dfhm,a.sc,a.xm,a.renp,a.gskx,a.bgi,a.cmsj,a.cgi,a.dh07,a.sjdd
from(select 呼叫日期 as hjrq,呼叫时间 as hjsj,用户号码通话所在地 as thd,thdzw,jzm,dip,用户号码 as yhhm ,主被叫标志 as txfs, 对方号码 as dfhm,通话时长 as sc,
xm,renp,gsd,gskx,cmsj,tb.cgi,tb.bgi,sjdd,tb.dh07
from tb
left join bmdh on tb.对方号码=bmdh.dh
left join bmsz on tb.bgi=bmsz.bgi
left join bmgs on tb.dh07=bmgs.dh07
where 对方号码<>' ' and 对方号码 not like'%10086%' and 对方号码 not like'%1065%'
and tb.cgi>'0' and 通话时长>'0')a
group by a.hjrq,a.hjsj,a.thd,a.thdzw,a.jzm,a.dip,a.yhhm,a.txfs,a.dfhm,a.sc,a.xm,a.renp,a.gskx,a.bgi,a.cmsj,a.cgi,a.dh07,a.sjdd