--> 测试数据:[tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT,[name] VARCHAR(100)) INSERT [tb] SELECT 1,'C:5,E:1;C:6,E:20' UNION ALL SELECT 2,'C:5,E:1,A:21;C:55,E:7,A:21' UNION ALL SELECT 3,'C:5,E:1;C:10,E:20' GO --> -- 假设你要更新的条件是分号前面的。 -- 如更新条件为 update [tb] set [name]='C:5,D:6,E:1'+right([name],len([name])-7) where left([name],7)='C:5,E:1' --如果不是这样就没必要往下面看了 -- 次方法适用于2005 2008 -- 可以把条件 name 改成 id -- 支持插入人一个值。 IF OBJECT_ID('p') IS NOT NULL DROP proc p GO create proc p @old_name varchar(100), @new_name varchar(100) as set nocount on beginIF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1 create table #t1(iden int identity ,id int,name varchar(10),value varchar(10)) IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2 create table #t2(id int,name varchar(10),value varchar(10)) IF OBJECT_ID('f_str',N'FN') IS NOT NULL DROP function f_str --存跟新前后的name值变化 IF OBJECT_ID('tempdb..#t3') IS NOT NULL DROP TABLE #t3 create table #t3(id int,new_name varchar(100))--第一次更新,取出更新后的值 update tb set [name]=@new_name+right([name],len([name])-len(@old_name)) output deleted.id,inserted.name into #t3 where left([name],len(@old_name))=@old_name --select * from #t3 --拆分分号前面的值 insert into #t1 select a.id, name=left(b.name,charindex(':',b.name)-1), value=right(b.name,len(b.name)-charindex(':',b.name)) from( select id, [name] = CONVERT(XML,'<ROOT><V>' + REPLACE(left(new_name,charindex(';',new_name)-1), ',', '</V><V>') + '</V></ROOT>') from #t3 )a outer apply ( select [name] = N.v.[value]('.', 'varchar(10)') from a.[name].nodes('/ROOT/V') N(v) )b --拆分分号后面的值 insert into #t2 select a.id, name=left(b.name,charindex(':',b.name)-1), value=right(b.name,len(b.name)-charindex(':',b.name)) from( select id, [name] = CONVERT(XML,'<ROOT><V>' + REPLACE(right(new_name,len(new_name)-charindex(';',new_name)), ',', '</V><V>') + '</V></ROOT>') from #t3 )a outer apply ( select [name] = N.v.[value]('.', 'varchar(10)') from a.[name].nodes('/ROOT/V') N(v) )b--进行查询合并重组 ;with cte as ( select a.iden,a.id,a.name as a_name,a.value as a_value,isnull(b.name,a.name) as b_name, b.value as b_value from #t1 a left join #t2 b on a.name=b.name and a.id=b.id ) ,cte2 as ( select id,a_name,a_value,b_name,b_value=isnull(b_value,(select top 1 b_value from cte where iden<t.iden and b_value is not null order by iden desc)) from cte t ) --select * from cte2 ,cte3 as ( select id,stuff((select ','+b_name+':'+b_value from cte2 where id=b.id for xml path('')),1,1,'') as name from cte2 b group by id ) --select * from cte3 update a set a.name=left(a.name,charindex(';',a.name))+b.name from tb a,cte3 b where a.id=b.iddrop table #t1,#t2,#t3end --测试 select * from tb exec p 'C:5,E:1','C:5,D:7,E:1' select * from tb exec p 'C:5,D:7,E:1','C:5,D:7,F:12,G:15,E:1' select * from tb /* id name ----------- ---------------------------------------------------------------------------------------------------- 1 C:5,D:7,F:12,G:15,E:1;C:6,D:6,F:6,G:6,E:20 2 C:5,D:7,F:12,G:15,E:1,A:21;C:55,D:55,F:55,G:55,E:7,A:21 3 C:5,D:7,F:12,G:15,E:1;C:10,D:10,F:10,G:10,E:20(3 行受影响) */
C,D,E 是我举得例子.
他们之间没有必然关系.D不一定要插入到C,E之间.在E后面也可以的.
悲剧啊~~~~
--就是两个值?
update tablename
set [Counts]='C:5,D:5,E:1;C:6,D:6,E:2'
where [Counts]='C:5,E:1;C:6,E:2'
但是需要一个while循环替换所有符合的字符串看楼主的数据库设计,建议还是尽早重新弄一下比较好。现在已经是噩梦了。
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[name] VARCHAR(100))
INSERT [tb]
SELECT 1,'C:5,E:1;C:6,E:20' UNION ALL
SELECT 2,'C:5,E:1,A:21;C:55,E:7,A:21' UNION ALL
SELECT 3,'C:5,E:1;C:10,E:20'
GO
-->
-- 假设你要更新的条件是分号前面的。
-- 如更新条件为 update [tb] set [name]='C:5,D:6,E:1'+right([name],len([name])-7) where left([name],7)='C:5,E:1'
--如果不是这样就没必要往下面看了
-- 次方法适用于2005 2008
-- 可以把条件 name 改成 id
-- 支持插入人一个值。
IF OBJECT_ID('p') IS NOT NULL DROP proc p
GO
create proc p
@old_name varchar(100),
@new_name varchar(100)
as
set nocount on
beginIF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1
create table #t1(iden int identity ,id int,name varchar(10),value varchar(10))
IF OBJECT_ID('tempdb..#t2') IS NOT NULL DROP TABLE #t2
create table #t2(id int,name varchar(10),value varchar(10))
IF OBJECT_ID('f_str',N'FN') IS NOT NULL DROP function f_str
--存跟新前后的name值变化
IF OBJECT_ID('tempdb..#t3') IS NOT NULL DROP TABLE #t3
create table #t3(id int,new_name varchar(100))--第一次更新,取出更新后的值
update tb set [name]=@new_name+right([name],len([name])-len(@old_name))
output deleted.id,inserted.name into #t3
where left([name],len(@old_name))=@old_name
--select * from #t3
--拆分分号前面的值
insert into #t1
select a.id,
name=left(b.name,charindex(':',b.name)-1),
value=right(b.name,len(b.name)-charindex(':',b.name))
from(
select id,
[name] = CONVERT(XML,'<ROOT><V>' + REPLACE(left(new_name,charindex(';',new_name)-1), ',', '</V><V>') + '</V></ROOT>')
from #t3
)a
outer apply (
select [name] = N.v.[value]('.', 'varchar(10)')
from a.[name].nodes('/ROOT/V') N(v)
)b
--拆分分号后面的值
insert into #t2
select a.id,
name=left(b.name,charindex(':',b.name)-1),
value=right(b.name,len(b.name)-charindex(':',b.name))
from(
select id,
[name] = CONVERT(XML,'<ROOT><V>' + REPLACE(right(new_name,len(new_name)-charindex(';',new_name)), ',', '</V><V>') + '</V></ROOT>')
from #t3
)a
outer apply (
select [name] = N.v.[value]('.', 'varchar(10)')
from a.[name].nodes('/ROOT/V') N(v)
)b--进行查询合并重组
;with cte as
(
select a.iden,a.id,a.name as a_name,a.value as a_value,isnull(b.name,a.name) as b_name,
b.value as b_value
from #t1 a left join #t2 b
on a.name=b.name and a.id=b.id
)
,cte2 as
(
select id,a_name,a_value,b_name,b_value=isnull(b_value,(select top 1 b_value from cte where iden<t.iden and b_value is not null order by iden desc))
from cte t
)
--select * from cte2
,cte3 as
(
select id,stuff((select ','+b_name+':'+b_value from cte2 where id=b.id for xml path('')),1,1,'') as name
from cte2 b group by id
)
--select * from cte3
update a set a.name=left(a.name,charindex(';',a.name))+b.name
from tb a,cte3 b where a.id=b.iddrop table #t1,#t2,#t3end
--测试
select * from tb
exec p 'C:5,E:1','C:5,D:7,E:1'
select * from tb
exec p 'C:5,D:7,E:1','C:5,D:7,F:12,G:15,E:1'
select * from tb
/*
id name
----------- ----------------------------------------------------------------------------------------------------
1 C:5,D:7,F:12,G:15,E:1;C:6,D:6,F:6,G:6,E:20
2 C:5,D:7,F:12,G:15,E:1,A:21;C:55,D:55,F:55,G:55,E:7,A:21
3 C:5,D:7,F:12,G:15,E:1;C:10,D:10,F:10,G:10,E:20(3 行受影响)
*/
经姐姐对面提醒
下面是真实数据,便于理解.历史数据
Customer:5,Escort:1;Customer:6,Escort:3;
Customer:8,Escort:3;Customer:8,Escort:6;
Customer:9,Escort:9;Customer:3,Escort:7;Customer:18,Escort:27;
Customer:2,Escort:7;Customer:1,Escort:3;更新后的数据
Customer:5,Escort:1,Total Customer:5;Customer:6,Escort:3,Total Customer:6;
Customer:8,Escort:3,Total Customer:3;Customer:8,Escort:6,Total Customer:8;
Customer:9,Escort:9,Total Customer:9;Customer:3,Escort:7,Total Customer:3;Customer:18,Escort:27,Total Customer:18;
Customer:2,Escort:7,Total Customer:7;Customer:1,Escort:3,Total Customer:1;
关于Total Customer1 无所谓位置
例如 Total Customer:5,Customer:5,Escort:1; 可以
Customer:5,Total Customer:5,Escort:1; 也可以
Customer:5,Escort:1,Total Customer:5; 也可以
2 数值和Customer数值要一样(历史数据这样处理)
例如 Customer:5,Escort:1,Total Customer:6; 是错误的
Customer:5,Escort:1,Total Customer:5; 是正确的
半路接手的项目,更改数据结构也是个大工程,迁移历史数据是跑不掉的.
大家帮忙解解!
实在不行,只有用C#做处理了.