更新从属帐号
现有表info_company
IF EXISTS (SELECT * FROM sysobjects WHERE [name]='info_company')
DROP TABLE info_company
BEGIN
CREATE TABLE info_company(
company_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
com_name nvarchar(120) NOT NULL,
conn_man NVARCHAR(15) NULL,
conn_Tel VARCHAR(16) NULL,
com_email VARCHAR(64) NULL,
com_create_time datetime NOT NULL,
is_famous BIT DEFAULT(0) NOT NULL,
com_level INT DEFAULT(1) NOT NULL ,
com_remark nvarchar(120) NULL
)
END
--------插入测试信息-------------------
BEGIN
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('神州数码','Mr.Liu','2012-9-20',1,5)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('重庆人才网','Mr.Li','2012-9-20',1,4)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('梦幻天方','Mr.Li','2012-9-20',1,3)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('平安保险','Mr.Li','2012-9-20',1,1)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('尚观科技','Mr.Li','2012-9-20',0,1)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('用友软件','Mr.Li','2012-9-20',0,1)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('金蝶软件','Mr.Li','2012-9-20',0,2)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('深天之星软件','Mr.Li','2012-9-20',0,3)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('上海小兵科技','Mr.Li','2012-9-20',1,5)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('百度','Mr.Li','2012-9-20',1,4)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('神州数码1','Mr.Liu','2012-9-20',1,5)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('重庆人才网2','Mr.Li','2012-9-20',1,4)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('梦幻天方3','Mr.Li','2012-9-20',1,3)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('平安保险4','Mr.Li','2012-9-20',1,1)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('尚观科技5','Mr.Li','2012-9-20',0,1)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('用友软件6','Mr.Li','2012-9-20',0,1)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('金蝶软件7','Mr.Li','2012-9-20',0,2)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('深天之星软件8','Mr.Li','2012-9-20',0,3)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('上海小兵科技9','Mr.Li','2012-9-20',1,5)
INSERT INTO info_company(com_name,conn_man,com_create_time,is_famous,com_level)VALUES('百度10','Mr.Li','2012-9-20',1,4)
END
GO
需求：在表ifno_company中可能有一些这样的帐号
ABCD,CD两个帐号的主帐号是B，B的主帐号是A
EFG，G的主帐号是F，F的主帐号是E
需要将CD的主帐号更新为A，G的主帐号更新为E
请写一个通用的sql语句进行更新。
分析：这个题最难的还是找到父账号，如果使用普通的 SQL来写，很难完成，一次想到了才有Case when then end 语句来作为条件判断。
代码如下：
1 UPDATE ic 2 SET ic.link_com_id = CASE WHEN ic2.link_com_id IS NULL THEN ic2.company_id ELSE ic2.link_com_id END 3 FROM info_company ic 4 LEFT JOIN info_company ic2 ON ic2.company_id=ic.link_com_id 5 WHERE ic.link_com_id IS NOT NULL
非常简洁的写法，可是性能上面损失的却多了点，不知道是福可以优化下？