求救，根据多ID更新多字段
求救，根据多ID更新多字段
需要传3个参数，2个ID(双主键)，1个时间。都是数组形式的
ID1 = '1,2,3,4 '
ID2 = '101,102,103,104 '
TDay = '2007-1-3,2007-1-4,2007-1-15,2007-2-6 '
每组都是相同数量的个数，比如都是4组数
例如：update [Table] set TDay = '2007-1-3 ' where ID1 =1 AND ID2 = 101
更新4组，何如在存储过程中实现啊，谢谢大家了！
[解决办法]
create table T(ID1 int, ID2 int, TDay datetime)
insert T select 1, 101, null
insert T select 2, 102, null
insert T select 3, 103, null
insert T select 4, 104, null
insert T select 5, 105, null
create proc pc(@ID1 varchar(200), @ID2 varchar(200), @TDay varchar(1000))
as
declare @tb table(ID1 int, ID2 int, TDay datetime)
while(charindex( ', ', @ID1)> 0)
begin
insert @tb select
substring(@ID1, 1, charindex( ', ', @ID1)-1),
substring(@ID2, 1, charindex( ', ', @ID2)-1),
substring(@TDay, 1, charindex( ', ', @TDay)-1)
select @ID1=stuff(@ID1, 1, charindex( ', ', @ID1), ' '),
@ID2=stuff(@ID2, 1, charindex( ', ', @ID2), ' '),
@TDay=stuff(@TDay, 1, charindex( ', ', @TDay), ' ')
end
insert @tb select @ID1, @ID2, @TDay
update T set TDay=B.TDay
from @tb as B
where T.ID1=B.ID1 and T.ID2=B.ID2
go
declare @ID1 varchar(200), @ID2 varchar(200), @TDay varchar(1000)
select
@ID1 = '1,2,3,4 ',
@ID2 = '101,102,103,104 ',
@TDay = '2007-1-3,2007-1-4,2007-1-15,2007-2-6 '
exec pc @ID1, @ID2, @TDay
go
select * from T
--result
ID1 ID2 TDay
----------- ----------- ------------------------------------------------------
1 101 2007-01-03 00:00:00.000
2 102 2007-01-04 00:00:00.000
3 103 2007-01-15 00:00:00.000
4 104 2007-02-06 00:00:00.000
5 105 NULL
[解决办法]
CREATE FUNCTION dbo.f_splitstr(
@str varchar(8000)
)RETURNS @r TABLE(id int IDENTITY(1, 1), value varchar(5000))
AS
BEGIN
DECLARE @pos int
SET @pos = CHARINDEX( ', ', @str)
WHILE @pos > 0
BEGIN
INSERT @r(value) VALUES(LEFT(@str, @pos - 1))
SELECT
@str = STUFF(@str, 1, @pos, ' '),
@pos = CHARINDEX( ', ', @str)
END
IF @str > ' '
INSERT @r(value) VALUES(@str)
RETURN
END
GO
-- 调用函数实现处理
DECLARE @id1s varchar(8000), @id2s varchar(8000), @dates varchar(8000)
SELECT
@id1s = '1,2,3,4 ',
@id2s = '101,102,103,104 ',
@dates= '2007-1-3,2007-1-4,2007-1-15,2007-2-6 '
--UPDATE A SET TDay = B.dt
--FROM [Table] A,(
SELECT
id1 = CONVERT(int, ID1.value),
id2 = CONVERT(int, ID2.value),
dt = CONVERT(datetime, DT.value)
FROM dbo.f_splitstr(@id1s) ID1, dbo.f_splitstr(@id2s) ID2, dbo.f_splitstr(@dates) DT
WHERE ID1.id = ID2.id
AND ID1.id = DT.id
--)B
--WHERE A.ID1 = B.ID1 AND A.ID2 = B.ID2
GO
DROP FUNCTION f_splitstr
[解决办法]
--创建一个分割字符串的函数
CREATE FUNCTION dbo.f_splitSTR(
@s varchar(8000), --要分拆的字符串
@split varchar(10), --数据分隔符
@pos int -- 取第几个
)RETURNS varchar(100)
AS
BEGIN
DECLARE @splitlen int, @re varchar(100)
SET @splitlen=LEN(@split+ 'a ') - 2
WHILE CHARINDEX(@split,@s) > 0 AND @pos > 0
SELECT
@re = LEFT(@s,CHARINDEX(@split,@s)-1),
@s=STUFF(@s,1,CHARINDEX(@split, @s)+@splitlen, ' '),
@pos = @pos - 1
RETURN(CASE
WHEN @pos = 0 THEN @re
WHEN @pos = 1 THEN @s
ELSE NULL END)
END
GO
--这个函数可以得到数组的个数
CREATE function getStrCount (@str varchar(8000),@splitstr varchar(100))
returns int
as
begin
declare @int_return int
declare @start int
declare @next int
declare @location int
select @next = 0
select @location = 1
if len(@str) > 0
select @int_return = 0
if charindex(@splitstr,@str) = 0
select @int_return =0
while (@location <> 0)
begin
select @start = @location + 1
select @location = charindex(@splitstr,@str,@start)
select @next = @next + 1
select @int_return = @next
end
return @int_return
end
declare @num int
select @num=dbo.getStrCount( '1,2,3,4,5,6 ', ', ')
print @num
create proc update_date(@ID1 varchar(1000),@ID2 varchar(1000),@TDay varchar(1000))
as
declare @Num int
declare @Cnt int
select @Num=dbo.getStrCount(@ID1, ', ')
set @Cnt = 1
while @Cnt <= @Num
begin
update [Table]
set TDay = (select dbo.f_splitSTR(@TDay, ', ', @Cnt))
where ID1 =(select dbo.f_splitSTR(@ID1, ', ', @Cnt)) AND ID2 = (select dbo.f_splitSTR(@ID2, ', ', @Cnt))
end
适适这个
