insert into tmp2 select Npkh,Ye from xktmpb a where Sj=(select max(sj) from xktmpb where Npkh=a.Npkh)
--下面是测试--表@xktmpb及数据 declare @xktmpb table(Npkh varchar(10),Ye decimal(20,2),Sj datetime) insert into @xktmpb select 'A0123',12.00,'2002-12-12' union all select 'A0123',10.00,'2002-12-13' union all select 'A0124',31.00,'2002-12-10' union all select 'A0124',10.00,'2002-12-12' union all select 'A0102',123.00,'2002-12-10'--表@tmp2 declare @tmp2 table(Npkh varchar(10),Ye decimal(20,2))--插入数据处理 insert into @tmp2 select Npkh,Ye from @xktmpb a where Sj=(select max(sj) from @xktmpb where Npkh=a.Npkh)--查询处理结果 select * from @tmp2/*--测试结果 Npkh Ye ---------- ---------------------- A0124 10.00 A0123 10.00 A0102 123.00(所影响的行数为 3 行) --*/
update tmp1 set [Sign]=case when a.Cs-1<>b.Cs then 2 else 0 end from tmp1 a join icxxb b on a.Npkh=b.Npkh
--下面是问题3的测试--测试数据 declare @icxxb table(Npkh varchar(10),Cs int) insert into @icxxb select 'A0123',12 union all select 'A0102',2declare @tmp1 table(Npkh varchar(10),Cs int,[Sign] int) insert into @tmp1 select 'A0123',13,0 union all select 'A0102',2,0--更新处理 update @tmp1 set [Sign]=case when a.Cs-1<>b.Cs then 2 else 0 end from @tmp1 a join @icxxb b on a.Npkh=b.Npkh--显示处理结果 select * from @tmp1/*--测试结果 Npkh Cs Sign ---------- ----------- ----------- A0123 13 0 A0102 2 2(所影响的行数为 2 行) --*/
--上面的有点乱,整理一下:--问题1. if exists(select 1 from 表 where 条件) print '在' else print '不在'--问题2. insert into tmp2 select Npkh,Ye from xktmpb a where Sj=(select max(sj) from xktmpb where Npkh=a.Npkh)--问题3. update tmp1 set [Sign]=case when a.Cs-1<>b.Cs then 2 else 0 end from tmp1 a join icxxb b on a.Npkh=b.Npkh
1.在存储过程中,如何判断一条记录是否在一表中? 要用表的关键字段进行判断,即值要唯一,可以是几个字段合起来是唯一 if exists(select 1 from 表 where 字段=。。 ) 2 update a set ye=b.ye from tmp2 a,xktmpb b where a.Npkh=b.Npkh and b.sj=(select max(sj) from xktmpb where Npkh=a.Npkh)3 update a set [Sign]=case when a.Cs-1<>b.Cs then 2 else 0 end from tmp1 a , icxxb b where a.Npkh=b.Npkh
select Npkh,Ye
from xktmpb a
where Sj=(select max(sj) from xktmpb where Npkh=a.Npkh)
declare @xktmpb table(Npkh varchar(10),Ye decimal(20,2),Sj datetime)
insert into @xktmpb
select 'A0123',12.00,'2002-12-12'
union all select 'A0123',10.00,'2002-12-13'
union all select 'A0124',31.00,'2002-12-10'
union all select 'A0124',10.00,'2002-12-12'
union all select 'A0102',123.00,'2002-12-10'--表@tmp2
declare @tmp2 table(Npkh varchar(10),Ye decimal(20,2))--插入数据处理
insert into @tmp2
select Npkh,Ye
from @xktmpb a
where Sj=(select max(sj) from @xktmpb where Npkh=a.Npkh)--查询处理结果
select * from @tmp2/*--测试结果
Npkh Ye
---------- ----------------------
A0124 10.00
A0123 10.00
A0102 123.00(所影响的行数为 3 行)
--*/
from tmp1 a join icxxb b on a.Npkh=b.Npkh
declare @icxxb table(Npkh varchar(10),Cs int)
insert into @icxxb
select 'A0123',12
union all select 'A0102',2declare @tmp1 table(Npkh varchar(10),Cs int,[Sign] int)
insert into @tmp1
select 'A0123',13,0
union all select 'A0102',2,0--更新处理
update @tmp1 set [Sign]=case when a.Cs-1<>b.Cs then 2 else 0 end
from @tmp1 a join @icxxb b on a.Npkh=b.Npkh--显示处理结果
select * from @tmp1/*--测试结果
Npkh Cs Sign
---------- ----------- -----------
A0123 13 0
A0102 2 2(所影响的行数为 2 行)
--*/
if exists(select 1 from 表 where 条件)
print '在'
else
print '不在'--问题2.
insert into tmp2
select Npkh,Ye
from xktmpb a
where Sj=(select max(sj) from xktmpb where Npkh=a.Npkh)--问题3.
update tmp1 set [Sign]=case when a.Cs-1<>b.Cs then 2 else 0 end
from tmp1 a join icxxb b on a.Npkh=b.Npkh
要用表的关键字段进行判断,即值要唯一,可以是几个字段合起来是唯一 if exists(select 1 from 表 where 字段=。。 )
2 update a
set ye=b.ye
from tmp2 a,xktmpb b
where a.Npkh=b.Npkh and b.sj=(select max(sj) from xktmpb where Npkh=a.Npkh)3
update a
set [Sign]=case when a.Cs-1<>b.Cs then 2 else 0 end
from tmp1 a , icxxb b
where a.Npkh=b.Npkh