两个表,
第一个表#temp01
id num sum
1 10
2 20第二个表#temp02
id num
1 10
2 20
3 30第一个表中的字段sum是依据关联字段id 来完成sum=表1的num-表2的num,现在表二多出一个id=3的,在第一个表中没有这个id,而我想要在表二中有,而在表一中没有的,如id=3,就直接把id=3的num赋值给表一的sum.请问怎么做。我写了一个游标,可以实现两个表的id都有的情况,可是表二中多出来的id=3就不行了。
declare @id varchar(50)
declare @num numeric(18,2)
DECLARE Employee_Cursor_out CURSOR FOR
SELECT id,num FROM #temp02
OPEN Employee_Cursor_out
FETCH NEXT FROM Employee_Cursor_out into @id,@num
WHILE @@FETCH_STATUS = 0
BEGIN
update #temp01 set sum=num-@num where id=@id
FETCH NEXT FROM Employee_Cursor_out into @id,@num
END
CLOSE Employee_Cursor_out
DEALLOCATE Employee_Cursor_out
第一个表#temp01
id num sum
1 10
2 20第二个表#temp02
id num
1 10
2 20
3 30第一个表中的字段sum是依据关联字段id 来完成sum=表1的num-表2的num,现在表二多出一个id=3的,在第一个表中没有这个id,而我想要在表二中有,而在表一中没有的,如id=3,就直接把id=3的num赋值给表一的sum.请问怎么做。我写了一个游标,可以实现两个表的id都有的情况,可是表二中多出来的id=3就不行了。
declare @id varchar(50)
declare @num numeric(18,2)
DECLARE Employee_Cursor_out CURSOR FOR
SELECT id,num FROM #temp02
OPEN Employee_Cursor_out
FETCH NEXT FROM Employee_Cursor_out into @id,@num
WHILE @@FETCH_STATUS = 0
BEGIN
update #temp01 set sum=num-@num where id=@id
FETCH NEXT FROM Employee_Cursor_out into @id,@num
END
CLOSE Employee_Cursor_out
DEALLOCATE Employee_Cursor_out
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-10 19:08:27
----------------------------------------------------------------
--> 测试数据:#temp01
if object_id('tempdb.dbo.#temp01') is not null drop table #temp01
create table #temp01([id] int,[num] int,[sum] sql_variant)
insert #temp01
select 1,10,null union all
select 2,20,null
--> 测试数据:#temp02
if object_id('tempdb.dbo.#temp02') is not null drop table #temp02
create table #temp02([id] int,[num] int)
insert #temp02
select 1,10 union all
select 2,20 union all
select 3,30
--------------开始查询--------------------------
select
a.id,a.num,
isnull(b.num-a.num,a.num) as num
from
[#temp02] a
left join
[#temp01] b
on
a.id=b.id----------------结果----------------------------
/*id num num
----------- ----------- -----------
1 10 0
2 20 0
3 30 30(所影响的行数为 3 行)*/
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-10 19:03:24
---------------------------------
--> 生成测试数据表:temp01If not object_id('[temp01]') is null
Drop table [temp01]
Go
Create table [temp01]([id] int,[num] int,[sum] int)
Insert temp01
Select 1,10,null union all
Select 2,20,null
Go
--Select * from temp01--> 生成测试数据表:temp02If not object_id('[temp02]') is null
Drop table [temp02]
Go
Create table [temp02]([id] int,[num] int)
Insert temp02
Select 1,10 union all
Select 2,20 union all
Select 3,30
Go
--Select * from temp02-->SQL查询如下:
--1.更新表一中的SUM
update temp01 set [sum]=temp01.[num]-b.num from temp02 b where temp01.id=b.id
--2.插入ID不在表一中存在的.
insert temp01 select id,0,num from temp02 t where not exists(select 1 from temp01 where id=t.id)
select * from temp01
/*
id num sum
----------- ----------- -----------
1 10 0
2 20 0
3 0 30(3 行受影响)
*/
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( id int, num int ,sum int)
go
insert tb(id,num) SELECT
1 , 10 UNION ALL SELECT
2 , 20
go
IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1( id int, num int)
go
insert tb1 SELECT
1 ,10 UNION ALL SELECT
2 ,20 UNION ALL SELECT
3 , 30
go
select id=isnull(t1.id,t2.id),t1.num,[SUM]=case when t1.num IS null then t2.num else t1.num-t2.num end
from tb t1 right join tb1 t2 on t1.id=t2.id
/*
(3 行受影响)
id num SUM
----------- ----------- -----------
1 10 0
2 20 0
3 NULL 30*/
isnull(a.id,b.id) as id,a.num,case when a.num is null then b.num else a.num-isnull(b.num,0) end as [sum]
from #temp01 afull join #temp02 b
on a.id=b.id
--1.更新表一中的SUM
update temp01 set [sum]=temp01.[num]-b.num from temp02 b where temp01.id=b.id
--2.插入ID不在表一中存在的.
insert temp01 select id,num,num from temp02 t where not exists(select 1 from temp01 where id=t.id)
select * from temp01
/*
id num sum
----------- ----------- -----------
1 10 0
2 20 0
3 30 30(3 行受影响)
*/还是要这样的结果.
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-10 19:08:27
----------------------------------------------------------------
--> 测试数据:#temp01
if object_id('tempdb.dbo.#temp01') is not null drop table #temp01
create table #temp01([id] int,[num] int,[sum] sql_variant)
insert #temp01
select 1,10,null union all
select 2,20,null
--> 测试数据:#temp02
if object_id('tempdb.dbo.#temp02') is not null drop table #temp02
create table #temp02([id] int,[num] int)
insert #temp02
select 1,10 union all
select 2,20 union all
select 3,30
--------------开始查询--------------------------
select
isnull(a.id,b.id) as id,a.num,
case when a.num is null then b.num else a.num-isnull(b.num,0) end as [sum]
from
#temp01 a
full join
#temp02 b
on a.id=b.id
----------------结果----------------------------
/*id num num
----------- ----------- -----------
1 10 0
2 20 0
3 30 30(所影响的行数为 3 行)*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-10 19:08:27
----------------------------------------------------------------
--> 测试数据:#temp01
if object_id('tempdb.dbo.#temp01') is not null drop table #temp01
create table #temp01([id] int,[num] int,[sum] sql_variant)
insert #temp01
select 1,10,null union all
select 2,20,null
--> 测试数据:#temp02
if object_id('tempdb.dbo.#temp02') is not null drop table #temp02
create table #temp02([id] int,[num] int)
insert #temp02
select 1,10 union all
select 2,20 union all
select 3,30
--------------开始查询--------------------------
select
isnull(a.id,b.id) as id,a.num,
case when a.num is null then b.num else a.num-isnull(b.num,0) end as [sum]
from
#temp01 a
full join
#temp02 b
on a.id=b.id
----------------结果----------------------------
/*id num sum
----------- ----------- -----------
1 10 0
2 20 0
3 NULL 30(所影响的行数为 3 行)
*/
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( id int, num int ,sum int)
go
insert tb(id,num) SELECT
1 , 10 UNION ALL SELECT
2 , 20
go
IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1( id int, num int)
go
insert tb1 SELECT
1 ,10 UNION ALL SELECT
2 ,20 UNION ALL SELECT
3 , 30
go
--更新好内容 放入临时表
select id=isnull(t1.id,t2.id),t1.num,[SUM]=case when t1.num IS null then t2.num else t1.num-t2.num end
into #
from tb t1 right join tb1 t2 on t1.id=t2.id
--清理原来表内容
truncate table tb
--插入新内容
insert tb
select * from #
select * from tb
/*
(3 行受影响)
id num SUM
----------- ----------- -----------
1 10 0
2 20 0
3 NULL 30*/