我有一个表A,它有五个字段Id ParentID UserName UserMobilePhone Parentphone,其中Parentphone字段是后来添加的,我想要实现在原有数据基础上生成这个字段的值,例如:原始数据为:
10 0 月上江南 15017071427
11 10 王磊 13403299782
12 11 yanguomin 13599569444
生成后的数据为:
10 0 月上江南 15017071427 ***********
11 10 王磊 13403299782 15017071427
12 11 yanguomin 13599569444 13403299782 不知道这个sql应该怎么写?请大家帮忙。
10 0 月上江南 15017071427
11 10 王磊 13403299782
12 11 yanguomin 13599569444
生成后的数据为:
10 0 月上江南 15017071427 ***********
11 10 王磊 13403299782 15017071427
12 11 yanguomin 13599569444 13403299782 不知道这个sql应该怎么写?请大家帮忙。
update tb set Parentphone=UserMobilePhone
set Parentphone =(select UserMobilePhone from a b where b.ParentID =a.ID)
set Parentphone=(select UserMobilePhone from tb A where tb.ParentID=A.Id)
insert into tb values(10, 0 ,'月上江南' , '15017071427','')
insert into tb values(11, 10 ,'王磊' , '13403299782','')
insert into tb values(12, 11 ,'yanguomin', '13599569444','')
goselect Id ,ParentID ,UserName ,UserMobilePhone,
Parentphone = (select top 1 UserMobilePhone from tb where id < t.id order by id desc)
from tb tdrop table tb/*
Id ParentID UserName UserMobilePhone Parentphone
----------- ----------- -------------------- -------------------- --------------------
10 0 月上江南 15017071427 NULL
11 10 王磊 13403299782 15017071427
12 11 yanguomin 13599569444 13403299782(所影响的行数为 3 行)
*/
insert into tb values(10, 0 ,'月上江南' , '15017071427','')
insert into tb values(11, 10 ,'王磊' , '13403299782','')
insert into tb values(12, 11 ,'yanguomin', '13599569444','')
goselect m.Id ,m.ParentID ,m.UserName ,m.UserMobilePhone , Parentphone = n.UserMobilePhone from tb m left join tb n
on m.parentid = n.id
/*
Id ParentID UserName UserMobilePhone Parentphone
----------- ----------- -------------------- -------------------- --------------------
10 0 月上江南 15017071427 NULL
11 10 王磊 13403299782 15017071427
12 11 yanguomin 13599569444 13403299782(所影响的行数为 3 行)
*/select Id ,ParentID ,UserName ,UserMobilePhone,
Parentphone = (select top 1 UserMobilePhone from tb where id < t.id order by id desc)
from tb t
/*
Id ParentID UserName UserMobilePhone Parentphone
----------- ----------- -------------------- -------------------- --------------------
10 0 月上江南 15017071427 NULL
11 10 王磊 13403299782 15017071427
12 11 yanguomin 13599569444 13403299782(所影响的行数为 3 行)
*/drop table tb
好像写倒了~
update a
set Parentphone =(select UserMobilePhone from a b where b.ID =a.ParentID)
如果你的数据库是2000或2005的话,可以直接在里面添加。如果要写SQL语句,那么,也分两种,一种是你添加的数据是一样的,只要写一句SQL,如果你要的值是不一样的,你可以写个方法,根据ID添加Parentphone数据,把ID作为参数传到SQL语句当中:update A set Parentphone =? where Id=参数