有以下表A,NBR列最大到5,要传入 1~5 个数来作为cnt 的新值,对于已经有的NBR更新,没有的NBR则插入新值:
原表:A
id NBR cnt
----------- ---------- ----------- ----------- -----------
1 1 20
1 2 30
1 3 20
2 1 20
2 2 20如果传入参数 id = 1, @cnt1 = 11 , @cnt2 = 1, @cnt3=1,@cnt4 = 20 , @cnt5 = 0 则结果为
id NBR cnt
----------- ---------- ----------- ----------- -----------
1 1 11
1 2 1
1 3 1
2 1 20
2 2 20
1 4 20
1 5 0
如果传入参数 id = 1, @cnt1 = 11 , @cnt2 = 1, @cnt3=1,@cnt4 = 20, @cnt5 = 22则结果为
id NBR cnt
----------- ---------- ----------- ----------- -----------
1 1 11
1 2 1
1 3 1
2 1 20
2 2 20
1 4 20
1 5 22
传入参数的数目是固定的,就是id , @cnt1 , @cnt2 , @cnt3,@cnt4, @cnt5 这6个
原表:A
id NBR cnt
----------- ---------- ----------- ----------- -----------
1 1 20
1 2 30
1 3 20
2 1 20
2 2 20如果传入参数 id = 1, @cnt1 = 11 , @cnt2 = 1, @cnt3=1,@cnt4 = 20 , @cnt5 = 0 则结果为
id NBR cnt
----------- ---------- ----------- ----------- -----------
1 1 11
1 2 1
1 3 1
2 1 20
2 2 20
1 4 20
1 5 0
如果传入参数 id = 1, @cnt1 = 11 , @cnt2 = 1, @cnt3=1,@cnt4 = 20, @cnt5 = 22则结果为
id NBR cnt
----------- ---------- ----------- ----------- -----------
1 1 11
1 2 1
1 3 1
2 1 20
2 2 20
1 4 20
1 5 22
传入参数的数目是固定的,就是id , @cnt1 , @cnt2 , @cnt3,@cnt4, @cnt5 这6个
insert into tablename(...) values(...)
else
update tablename set ... where ...
create table #temp
(
id int,
NBR int,
cnt int
)
insert #temp
select 1, 1, 20 union all
select 1, 2, 20 union all
select 1, 3, 20 union all
select 2, 1, 20 union all
select 2, 2, 20
go
--SQL:
declare
@id int,
@cnt1 int,
@cnt2 int,
@cnt3 int,
@cnt4 int,
@cnt5 int
select
@id = 1,
@cnt1 = 11 ,
@cnt2 = 1,
@cnt3=1,
@cnt4 = 20,
@cnt5 = 0
--SQL:
declare @table table
(
rowno int,
id int,
new_cnt int,
NBR int,
old_cnt int
)
insert @table
select a.rowno,a.id, new_cnt=a.cnt, b.NBR, old_cnt=b.cnt from
(
select rowno = ROW_NUMBER() over(order by getdate()), * FROM
(
select id = @id, cnt = @cnt1 union all
select id = @id, cnt = @cnt2 union all
select id = @id, cnt = @cnt3 union all
select id = @id, cnt = @cnt4 union all
select id = @id, cnt = @cnt5
)T
) a
LEFT JOIN #temp b
ON a.id = b.id and a.rowno = b.NBRinsert into #temp
select id, rowno, new_cnt from @table
where NBR is null and old_cnt is nullupdate A
set cnt = new_cnt
from #temp A
inner join
@table b
on a.id = b.id and a.NBR = b.rowno
GO
--RESULT:
select * from #temp
/*
id NBR cnt
1 1 11
1 2 1
1 3 1
2 1 20
2 2 20
1 4 20
1 5 0
*/
begin
update ....
end
else
begin
insert ....
end
后面4句一样
create table #A (id int, NBR int, cnt int)
insert #A select 1 ,1 ,20 union all
select 1 ,2 ,30 union all
select 1 ,3, 20 union all
select 2 ,1, 20 union all
select 2 ,2, 20
create proc TestProc
@id int,
@cnt1 int,
@cnt2 int,
@cnt3 int,
@cnt4 int,
@cnt5 int
as
declare @Temp table(nbr int,cnt int)
insert @Temp
select 1,@cnt1 union all
select 2,@cnt2 union all
select 3,@cnt3 union all
select 4,@cnt4 union all
select 5,@cnt5
update #A set #A.cnt=t.cnt from @Temp as T where #A.id=@id and t.nbr=#A.nbr
insert #A select @id,nbr,cnt from @Temp as t where
not exists (select 1 from #A as a where @id=a.id and a.nbr=t.nbr)
這樣行麼?