sqlserver学习--4（replace()，rank()，dense_rank()，update,merge）

1.replace()函数

UPDATE tmp_create_fminfo SET bore=REPLACE(REPLACE(bore,'（',''),'）','')
--REPLACE(bore,'（','')：bore字段的左括号以空代替
--REPLACE(REPLACE(bore,'（',''),'）','')：REPLACE(bore,'（','')的右括号以空代替

2.update语法

单表情况：

update tbluserinfo set usertype = '232423' from tbluserinfo where userid = '22222' and createtime > '2017-07-12'

多表关联更新-->相当于merge语法

update tbluserinfo set usertype = 用水类别 
from tbluserinfo,tmp_1
where userid = 户号 and createtime > '2017-07-12'

update tbluserinfo 
   set usertype = 用水类别,
       useraddress = isnull(地址,''),
       installdate = CONVERT(varchar(10),立户日期,121)
  from tbluserinfo,tmp_1
 where userid = 户号 and createtime > '2017-07-12'

或

MERGE INTO tbluserinfo as info
USING tmp_1 as tmp ON info.userid = tmp.户号 and info.createtime > '2017-07-12'
WHEN MATCHED THEN 
update set
　　info.usertype = tmp.用水类别,
　　info.useraddress = tmp.地址,
　　info.installdate = tmp.立户日期

 rank() ,dense_rank()

注：rank不连续，若一样，占据同一个名次，接下来的数据按照同名次总数+1的方式继续往下排。比如1,1,3,3,3,6,7

　　dense_rank连续，若一样，占据同一个名次，接下来的数据按照下一名次继续往下排。比如1,1,1,2,2,3

select top 100 PID,userid,simid,fmaddress,rank() over(order by simid desc) as rank,dense_rank() over(order by simid desc) as dense_rank
from tblfmreadinfo