我有一个最终数据表,tableEnd
字段1 字段2(唯一) 字段3 字段4 字段5
name No Desc classs stu
某某 1 123,456,1254,123456 102
小明 2 5321,123
和一个我已经处理好的表tableRaw
字段1 字段2(唯一) 字段3
name No Desc
某某 1 123
小明 2 5321
某某 1 555
某某 1 666
小黑 3 456
最终得到tableEed里面的数据变为
字段1 字段2(唯一) 字段3 字段4 字段5
name No Desc classs stu
某某 1 123,456,1254,123456,555,666 102
小明 2 5321,123
小黑 3 456
就是,如果,tableRaw中的No在tableEnd的No中存在,则将tableRaw的Desc添加到tableEnd中,不存在则添加一行新的数据.
我本来是使用select into.但是,这样只能够将我要的数据创建到一个新表中,而不能够将数据更新到我的数据表,求指教
------解决方案--------------------
- SQL code
create table tableEnd(name varchar(6), Nos int, Descs varchar(50))insert into tableEndselect '某某', 1, '123,456,1254,123456' union allselect '小明', 2, '5321,123' create table tableRaw(name varchar(6), Nos int, Descs varchar(50)) insert into tableRawselect '某某', 1, '123' union allselect '小明', 2, '5321' union allselect '某某', 1, '555' union allselect '某某', 1, '666' union allselect '小黑', 3, '456'select * from tableEndname Nos Descs------ ----------- --------------------------------------------------某某 1 123,456,1254,123456小明 2 5321,123select * from tableRawname Nos Descs------ ----------- --------------------------------------------------某某 1 123小明 2 5321某某 1 555某某 1 666小黑 3 456;with t as(select a.name,a.Nos,substring(a.Descs,b.number,charindex(',',a.Descs+',',b.number)-b.number) Descs from tableEnd ainner join master.dbo.spt_values bon b.[type]='P' and substring(','+a.Descs,b.number,1)=','unionselect name,Nos,Descs from tableRaw)select name,Nos,stuff((select ','+Descs from t t2 where t2.name=t.name and t2.Nos=t.Nos for xml path('')),1,1,'') Descsfrom tgroup by name,Nosname Nos Descs------ ----------- -------------------------------某某 1 123,123456,1254,456,555,666小明 2 123,5321小黑 3 456(3 row(s) affected)
------解决方案--------------------
- SQL code
create table tableEnd(name varchar(6), Nos int, Descs varchar(50))insert into tableEndselect '某某', 1, '123,456,1254,123456' union allselect '小明', 2, '5321,123' create table tableRaw(name varchar(6), Nos int, Descs varchar(50)) insert into tableRawselect '某某', 1, '123' union allselect '小明', 2, '5321' union allselect '某某', 1, '555' union allselect '某某', 1, '666' union allselect '小黑', 3, '456'select * from tableEndname Nos Descs------ ----------- --------------------------------------------------某某 1 123,456,1254,123456小明 2 5321,123select * from tableRawname Nos Descs------ ----------- --------------------------------------------------某某 1 123小明 2 5321某某 1 555某某 1 666小黑 3 456;with t as(select a.name,a.Nos,substring(a.Descs,b.number,charindex(',',a.Descs+',',b.number)-b.number) Descs from tableEnd ainner join master.dbo.spt_values bon b.[type]='P' and substring(','+a.Descs,b.number,1)=','unionselect name,Nos,Descs from tableRaw)merge tableEnd as ousing(select name,Nos,stuff((select ','+Descs from t t2 where t2.name=t.name and t2.Nos=t.Nos for xml path('')),1,1,'') Descsfrom t group by name,Nos) as son o.Nos=s.Nos and o.name=s.namewhen matched then update set o.Descs=s.Descswhen not matched then insert(name,Nos,Descs) values(name,Nos,Descs);-- 结果select * from tableEndname Nos Descs------ ----------- --------------------------------------------------某某 1 123,123456,1254,456,555,666小明 2 123,5321小黑 3 456(3 row(s) affected)