62,041
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[表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] B
SELECT * FROM DB_B..[表C]
/*
BH ID LX VALUE
YG_01 01 1 500
YG_01 03 3 200
YG_01 05 1 300
*/