有3個數據庫DB_A,DB_B,DB_C,分別有相同結構的表A 表A有字段:a 員工序號,b 薪資序號,c 加減代號,d 金額 條件:比較DB_A,DB_B表A金額匯總的大小,然后將金額大的記錄導入到DB_C的表A 例如:DB_A:表A: 1為加項 2,3為減項 YG_01 01 1 100
YG_01 03 1 100
YG_01 02 2 20 例如:DB_B:表A: YG_01 01 1 500
YG_01 03 3 200
YG_01 05 1 300 DB_A金額為:180,DB_B金額:600,結果就是將DB_B的三條記錄導入DB_C的表A中
declare @totalB int
select @totalA=sum(金额) from DB_A:表A
select @totalB=sum(金额) from DB_B:表B
if(@totalA>@totalB)
begin
INSERT INTO DB_C(PriKey, Description)
SELECT ForeignKey, Description
FROM DB_A;
end
else
begin
INSERT INTO DB_C(PriKey, Description)
SELECT ForeignKey, Description
FROM DB_B;
end类似如此,你对应的改改就是的了 。
但是这又跟数据库DB_A有什么关系,跟他们的DB_A金額為:180,DB_B金額:600有什么关系?
解释清楚点
--> 测试数据:[表A]
DB_A
if object_id('[表A]') is not null drop table [表A]
create table [表A]([BH] varchar(5),[ID] varchar(2),[LX] int,[VALUE] int)
insert [表A]
select 'YG_01','01',1,100 union all
select 'YG_01','03',1,100 union all
select 'YG_01','02',2,20
--DB_B
--> 测试数据:[表B]
if object_id('[表B]') is not null drop table [表B]
create table [表B]([BH] varchar(5),[ID] varchar(2),[LX] int,[VALUE] int)
insert [表B]
select 'YG_01','01',1,500 union all
select 'YG_01','03',3,200 union all
select 'YG_01','05',1,300
--DB_C
if object_id('[表C]') is not null drop table [表C]
create table [表C]([BH] varchar(5),[ID] varchar(2),[LX] int,[VALUE] int)
INSERT DB_C..[表C]
SELECT DISTINCT
case WHEN(select SUM(case when lx=1 then [VALUE] else -[VALUE] end) from DB_A..[表A])
-(select SUM(case when lx=1 then [VALUE] else -[VALUE] end) from DB_B..[表B])>0
then A.BH else B.BH END,
case WHEN(select SUM(case when lx=1 then [VALUE] else -[VALUE] end) from DB_A..[表A])
-(select SUM(case when lx=1 then [VALUE] else -[VALUE] end) from DB_B..[表B])>0
then A.ID else B.ID END,
case WHEN(select SUM(case when lx=1 then [VALUE] else -[VALUE] end) from DB_A..[表A])
-(select SUM(case when lx=1 then [VALUE] else -[VALUE] end) from DB_B..[表B])>0
then A.LX else B.LX END,
case WHEN(select SUM(case when lx=1 then [VALUE] else -[VALUE] end) from DB_A..[表A])
-(select SUM(case when lx=1 then [VALUE] else -[VALUE] end) from DB_B..[表B])>0
then A.[VALUE] else B.[VALUE] END FROM DB_A..[表A] A,DB_B..[表B] BSELECT * FROM DB_B..[表C]
/*
BH ID LX VALUE
YG_01 01 1 500
YG_01 03 3 200
YG_01 05 1 300
*/
然后將汇总金额较大的明细导入到DB_C的表A 例如:DB_A:表A: 1为加项 2,3为減项 YG_01 01 1 600 2012-3-1
YG_01 03 3 500 2012-3-1
YG_01 05 1 300 2012-3-1
YG_01 01 1 100 2012-4-1
YG_01 03 1 100 2012-4-1
YG_01 02 2 20 2012-4-1
YG_02 01 1 50 2012-4-1
。。 例如:DB_B:表A: YG_01 01 1 500 2012-4-1
YG_01 03 3 200 2012-4-1
YG_01 05 1 300 2012-4-1
YG_02 01 1 20 2012-4-1结果:
select * from DB_C.DBO.表A
YG_01 01 1 500
YG_01 03 3 200
YG_01 05 1 300
YG_02 01 1 50
--paste
--filter NY=#2012-4-1#
--v2t
--group yg,DH,LX;JE=max(child.JE)
--sort YG,DH结果:
YG DH LX JE
YG_01 01 1 500
YG_01 02 2 20
YG_01 03 1 100
YG_01 03 3 200
YG_01 05 1 300
YG_02 01 1 50http://blog.csdn.net/yyz985/article/details/7400360数据:
YG DH LX JE NY
YG_01 01 1 600 2012-3-1
YG_01 03 3 500 2012-3-1
YG_01 05 1 300 2012-3-1
YG_01 01 1 100 2012-4-1
YG_01 03 1 100 2012-4-1
YG_01 02 2 20 2012-4-1
YG_02 01 1 50 2012-4-1
YG_01 01 1 500 2012-4-1
YG_01 03 3 200 2012-4-1
YG_01 05 1 300 2012-4-1
YG_02 01 1 20 2012-4-1
select @Res =
case when ((select ((select sum(d) from DB_A where c='1')-(select sum(d) from DB_A where c in('2','3')))) > (select ((select sum(d) from DB_B where c='1')-(select sum(d) from DB_B where c in('2','3')))) )
then
'A大于B'
else
'B大于A'
end
select @Res
判断完后直接插入就可以了
insert into DB_C select * from DB_A
或insert into DB_C select * from DB_B