实例应用

[Alessandro_]

表A：计划表
LX    MMMC     FS        JHJE  
1     XM1      A         1000.00
1     XM1      B         500.00
1     XM2      A         600.00
1     XM3      A         800.00
1     XM4      A         700.00
表B:执行表
LX    MMMC     FS        ZXJE  
1     XM1      A         500.00
1     XM1      B         200.00
1     XM2      A         300.00
1     XM3      A         400.00
结果表：
LX    MMMC     FS        JYJE  
1     XM1      A         500.00
1     XM1      B         300.00
1     XM2      A         300.00
1     XM3      A         400.00
1     XM4      A         700.00

如果没有执行，那么在表B：执行表 是不会出现数据的，如：XM4
但是结果表，是需要没有执行的结余数据的，如:XM4 结余金额700.00

求个结果表SQL：

[极品老土豆]

if(object_id('a')is not null) drop table a
go
create table a
(
LX INT,
MMMC VARCHAR(3),
FS VARCHAR(1),
JHJE DECIMAL(18,2)
)
GO
INSERT INTO A
SELECT 1,'XM1','A',1000.00 UNION ALL 
SELECT 1,'XM1','B',500.00 UNION ALL 
SELECT 1,'XM2','A',600.00 UNION ALL 
SELECT 1,'XM3','A',800.00 UNION ALL 
SELECT 1,'XM4','A',700.00  
GO
IF(OBJECT_ID('B')IS NOT NULL) DROP TABLE B
GO
create table B
(
LX INT,
MMMC VARCHAR(3),
FS VARCHAR(1),
ZXJE DECIMAL(18,2)
)
GO
INSERT INTO B  
SELECT 1,'XM1','A',500.00 UNION ALL 
SELECT 1,'XM1','B',200.00 UNION ALL 
SELECT 1,'XM2','A',300.00 UNION ALL 
SELECT 1,'XM3','A',400.00 
go

select a.lx,a.mmmc,a.fs,case when b.zxje is not null then b.zxje else a.jhje end as JYJE 
from a left outer join b on a.MMMC=b.MMMC and a.fs = b.fs
/*
lx          mmmc fs   JYJE
----------- ---- ---- ---------------------------------------
1           XM1  A    500.00
1           XM1  B    200.00
1           XM2  A    300.00
1           XM3  A    400.00
1           XM4  A    700.00

(5 行受影响)

*/

[百年树人]

select a.lx,a.mmmc,a.fs,a.jhje-isnull(b.zxje,0) as jyje
from a
left join(
select lx,mmmc,fs,sum(zxje) as zxje from b group by lx,mmmc,fs
) b 
on a.lx=b.lx and a.mmmc=b.mmmc and a.fs=b.fs

/**
lx          mmmc fs   jyje
----------- ---- ---- ---------------------------------------
1           XM1  A    500.00
1           XM1  B    300.00
1           XM2  A    300.00
1           XM3  A    400.00
1           XM4  A    700.00

(5 行受影响)
**/

[骑着蜗牛去爬山]

with tb1(LX,MMMC,FS,JHJE)
as(
SELECT 1,'XM1','A',1000.00 UNION ALL 
SELECT 1,'XM1','B',500.00 UNION ALL 
SELECT 1,'XM2','A',600.00 UNION ALL 
SELECT 1,'XM3','A',800.00 UNION ALL 
SELECT 1,'XM4','A',700.00),
tb2(LX,MMMC,FS,ZXJE)
as(
SELECT 1,'XM1','A',500.00 UNION ALL 
SELECT 1,'XM1','B',200.00 UNION ALL 
SELECT 1,'XM2','A',300.00 UNION ALL 
SELECT 1,'XM3','A',400.00)
select tb11.lx,tb11.mmmc,tb11.fs, tb11.jhje-isnull(
(select tb2.zxje from tb2 where tb2.lx=tb11.lx and tb2.mmmc=tb11.mmmc and tb2.fs=tb11.fs),0)
jhje from tb1 tb11

[Alessandro_]

引用 2 楼 josy 的回复:

SQL code?123456789101112131415161718select a.lx,a.mmmc,a.fs,a.jhje-isnull(b.zxje,0) as jyjefrom aleft join(select lx,mmmc,fs,sum(zxje) as zxje from b group by lx,mmmc,fs) b on a.lx=b.lx a……

树哥啊，不知道是我数据问题还是哪的问题。 我照你这样写，结余金额多了15万多。

[骑着蜗牛去爬山]

引用 4 楼 xl_smlie 的回复:

我照你这样写，结余金额多了15万多。

多了15万？花不了给我点

[Alessandro_]

引用 2 楼 josy 的回复:

SQL code?123456789101112131415161718select a.lx,a.mmmc,a.fs,a.jhje-isnull(b.zxje,0) as jyjefrom aleft join(select lx,mmmc,fs,sum(zxje) as zxje from b group by lx,mmmc,fs) b on a.lx=b.lx a……

我擦，我在计划表，里面查询到有负数的计划：一共165193.47 支付数据里面是没有负数的 然后你这样写：结余多了 155193.47