昨天的问题有一点没解决好：用SQL语句让一个表左联接另一个表形成一个新表并增加一列余数

Question

左表shangji　　　A　　　B　　　　C 
　　　      　111号　　农业　　80000.00 
　　　      　112号　　林业　　30000.00 
　　      　　113号　　医疗　　40000.00 右表benjifenpei　　　D　　　　E　　　　F　　　G　　 
　　　　            111号　　001号　　农业　　10000.00 
　　　　　　　　　 　111号　　002号　　农业　　60000.00 
　　　　　　　　　　 112号　　003号　　林业　　30000.00 
专家给出的SQL语句是：select a.*,b.*,余款=a.c-isnull(c.c,0)
from shangji a left join benjifenpei b on a.a= b.d
left join (select d,c=sum(g) from benjifenpei group by d) c
on a.a = c.d
原来需要解决的问题： 
　　　　（1）111号分配两次，得到的余款数不正确，应该是10000.00元，能否分配一次后，用分配后的余额再进行第二次分配； 
　　　　（2）113号没有分配，想得到的结果是在“余款”里显示“40000.00”，估计是由于SQL语句用的是左连接，这样就无法进行相减。基本解决问题了！但
得出的表 　　　　　A      B      C      　　　D      E      F        G    　　　　余款　 
      111号　　农业　　80000.00　　111号　　001号　　农业　　　10000.00　　　10000.00 
　　　　111号　　农业　　80000.00　　111号　　002号　　农业　　　60000.00　　　10000.00 
　　　　112号　　林业　　30000.00　　112号　　003号　　林业　　　30000.00　　　　　0.00 
　　　　113号　　医疗　　40000.00　　　　　　　　　　　　　　　　　　　　　　　　40000.00　　　　新的问题：
　　表中出现的红色余款部分出现了两次10000.00元，能不能只出现一次10000.00元？因为如果出现两次10000.00元，导出到excel表中然后再求合计的话会出现错误。
谢谢专家！　　 

Answer 1

这样？
--> 测试数据: @shangji
declare @shangji table (A varchar(5),B varchar(4),C numeric(7,2))
insert into @shangji
select '111号','农业',80000.00 union all
select '112号','林业',30000.00 union all
select '113号','医疗',40000.00
--> 测试数据: @benjifenpei
declare @benjifenpei table (D varchar(5),E varchar(5),F varchar(4),G numeric(7,2))
insert into @benjifenpei
select '111号','001号','农业',10000.00 union all
select '111号','002号','农业',60000.00 union all
select '112号','003号','林业',30000.00select * from @shangji
select * from @benjifenpei
select a.*,d,f,余款=a.c-isnull(c.g,0) 
from @shangji a 
left join 
(select d,f,g=sum(g) from @benjifenpei group by d,f) c 
on a.a = c.d 

Answer 2

 
/******************************************************************************/
/*回复：20080605001总：00062                                                   */
/*主题：计算题                                                                            */
/*作者：二等草                                                                            */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
 
create table [shangji] ([A] varchar(5),[B] varchar(4),[C] numeric(7,2))
 insert into [shangji] select '111号','农业',80000.00
 insert into [shangji] select '112号','林业',30000.00
 insert into [shangji] select '113号','医疗',40000.00
 
create table [benjifenpei] ([D] varchar(5),[E] varchar(5),[F] varchar(4),[G] numeric(7,2))
 insert into [benjifenpei] select '111号','001号','农业',10000.00
 insert into [benjifenpei] select '111号','002号','农业',60000.00
 insert into [benjifenpei] select '112号','003号','林业',30000.00
go--代码--------------------------------------------------------------------------
select a.*,b.*,
余额=case when exists(select 1 from benjifenpei where d=b.d and e>b.e)
          then null
          else a.c-isnull(c.c,0) end
from shangji a left join benjifenpei b on a.a = b.d
               left join (select d,c=sum(g) from benjifenpei group by d) c on a.a=c.d
go/*结果--------------------------------------------------------------------------
A       B      C         D     E      F       G         余额                                       
-----  ----    ------- ----- -----   ----    --------- ---------------------------------------- 
111号  农业    80000.00 111号  001号  农业    10000.00   NULL
111号  农业    80000.00 111号  002号  农业    60000.00   10000.00 
112号  林业    30000.00 112号  003号  林业    30000.00   .00 
113号  医疗    40000.00 NULL   NULL   NULL    NULL      40000.00 
--清除------------------------------------------------------------------------*/
--drop table shangji,benjifenpei

Answer 3

select a.*, b.*, 余款=a.c-(select isnull(sum(g),0) from benjifenpei where d=b.d and e<=b.e)
from shangji a left join benjifenpei b on a.a= b.d

Answer 4

--> 测试数据: shangji
if object_id('shangji') is not null drop table shangji
create table shangji (A varchar(11),B varchar(11),C numeric(7,2))
insert into shangji
select '111号','农业',80000.00 union all
select '112号','林业',30000.00 union all
select '113号','医疗',40000.00
--> 测试数据: benjifenpei
if object_id('benjifenpei') is not null drop table benjifenpei
create table benjifenpei (D varchar(11),E varchar(11),F varchar(11),G numeric(7,2))
insert into benjifenpei
select '111号','001号','农业',10000.00 union all
select '111号','002号','农业',60000.00 union all
select '112号','003号','林业',30000.00select a.*, b.*, 余款=a.c-(select isnull(sum(g),0) from benjifenpei where d=b.d and e<=b.e)
from shangji a left join benjifenpei b on a.a= b.d/*
A           B           C                                       D           E           F           G                                       余款
----------- ----------- --------------------------------------- ----------- ----------- ----------- --------------------------------------- ---------------------------------------
111号      农业         80000.00                                111号       001号       农业        10000.00                                70000.00
111号      农业         80000.00                                111号       002号       农业        60000.00                                10000.00
112号      林业         30000.00                                112号       003号       林业        30000.00                                0.00
113号      医疗         40000.00                                NULL        NULL        NULL        NULL                                    40000.00
*/--> 删除测试
drop table shangji,benjifenpei

Answer 5

if object_id('shangji') is not null drop table shangji
create table shangji (A varchar(11),B varchar(11),C numeric(7,2))
insert into shangji
select '111号','农业',80000.00 union all
select '112号','林业',30000.00 union all
select '113号','医疗',40000.00
if object_id('benjifenpei') is not null drop table benjifenpei
create table benjifenpei (D varchar(11),E varchar(11),F varchar(11),G numeric(7,2))
insert into benjifenpei
select '111号','001号','农业',10000.00 union all
select '111号','002号','农业',60000.00 union all
select '112号','003号','林业',30000.00--> 要最好的余额，把 and e<=b.e 这个条件去掉，不过我觉得我 7 楼的答案更正确。
select a.*, b.*, 余款=a.c-(select isnull(sum(g),0) from benjifenpei where d=b.d /*and e<=b.e*/)
from shangji a left join benjifenpei b on a.a= b.d/*
A           B           C                                       D           E           F           G                                       余款
----------- ----------- --------------------------------------- ----------- ----------- ----------- --------------------------------------- ---------------------------------------
111号      农业         80000.00                                111号       001号       农业        10000.00                                10000.00
111号      农业         80000.00                                111号       002号       农业        60000.00                                10000.00
112号      林业         30000.00                                112号       003号       林业        30000.00                                0.00
113号      医疗         40000.00                                NULL        NULL        NULL        NULL                                    40000.00
*/drop table shangji,benjifenpei

Answer 6

if object_id('shangji') is not null drop table shangji
create table shangji (A varchar(11),B varchar(11),C numeric(7,2))
insert into shangji
select '111号','农业',80000.00 union all
select '112号','林业',30000.00 union all
select '113号','医疗',40000.00
if object_id('benjifenpei') is not null drop table benjifenpei
create table benjifenpei (D varchar(11),E varchar(11),F varchar(11),G numeric(7,2))
insert into benjifenpei
select '111号','001号','农业',10000.00 union all
select '111号','002号','农业',60000.00 union all
select '112号','003号','林业',30000.00--> 要最好的余额，把 and e<=b.e 这个条件去掉，不过我觉得我 7 楼的答案更正确。
select a.*, b.*, 余款=a.c-isnull(b.g,0)
from shangji a left join (select d, isnull(sum(g),0)g from benjifenpei group by d) b on a.a= b.d/*
A           B           C                                       D           E           F           G                                       余款
----------- ----------- --------------------------------------- ----------- ----------- ----------- --------------------------------------- ---------------------------------------
111号      农业         80000.00                                111号       002号       农业        60000.00                                10000.00
112号      林业         30000.00                                112号       003号       林业        30000.00                                0.00
113号      医疗         40000.00                                NULL        NULL        NULL        NULL                                    40000.00
*/drop table shangji,benjifenpei

Answer 7

select a.*,b.*,
余额=case when exists(select 1 from benjifenpei where d=b.d and e>b.e)
          then null
          else a.c-isnull(c.c,0) end
from shangji a left join benjifenpei b on a.a = b.d
               left join (select d,c=sum(g) from benjifenpei group by d) c on a.a=c.d
go
不好意思，标红的地方没明白指的是什么？在我的程序里调试不通过，提示“＝附近有错误”，还要麻烦专家了！

Answer 8

d －》benjifenpei 的字段d
e - 》benjifenpei 的字段e

Answer 9

不好意思，标红的地方没明白指的是什么？在我的程序里调试不通过，提示“＝附近有错误”，还要麻烦专家了！
---------------------------------------------------------------------------------------
我给的结果是运行结果，在SQL 2000 下没问题的。
要么你不是MS SQL 
要么你换了字段名称。

Answer 10

根据您的解决方案对我的SQL进行的修改，提示“＝附近有错误”，麻烦您再看一下SELECT
 zhuankuanlaikuan.上级文号, 
zhuankuanlaikuan.来款项目, 
zhuankuanlaikuan.科目编码, 
zhuankuanlaikuan.科目名称, 
zhuankuanlaikuan.来款金额, 
zhuankuanlaikuan.资金分类, 
zhuankuanlaikuan.备注, 
yusuanxiada.指标文号, 
yusuanxiada.单位名称, 
yusuanxiada.补助内容, 
yusuanxiada.补助金额,
            剩余专款＝case when 
exists (select  1  from  yusuanxiada  
where  上级文号= yusuanxiada.上级文号 and　指标文号> yusuanxiada.指标文号) 
 then null  else　　zhuankuanlaikuan.来款金额 - ISNULL(C.C, 0) AS 剩余专款 FROM zhuankuanlaikuan LEFT OUTER JOIN yusuanxiada ON zhuankuanlaikuan.上级文号 = yusuanxiada.上级文号 AND zhuankuanlaikuan.上级文号> '0' LEFT OUTER JOIN (SELECT 上级文号, SUM(补助金额) AS C FROM yusuanxiada GROUP BY 上级文号) AS C ON zhuankuanlaikuan.上级文号 = C.上级文号

Answer 11

上级文号= yusuanxiada.上级文号
---------------------------------
这里必须用别名，因为是自引用。

Answer 12

SELECT 
zhuankuanlaikuan.上级文号, 
zhuankuanlaikuan.来款项目, 
zhuankuanlaikuan.科目编码, 
zhuankuanlaikuan.科目名称, 
zhuankuanlaikuan.来款金额, 
zhuankuanlaikuan.资金分类, 
zhuankuanlaikuan.备注, 
yusuanxiada.指标文号, 
yusuanxiada.单位名称, 
yusuanxiada.补助内容, 
yusuanxiada.补助金额, 
            剩余专款＝case when 
exists (select  1  from  yusuanxiada  
where  上级文号= yusuanxiada.上级文号 and　指标文号> yusuanxiada.指标文号) 
then null  else　　zhuankuanlaikuan.来款金额 - ISNULL(C.C, 0) AS 剩余专款 FROM zhuankuanlaikuan LEFT OUTER JOIN yusuanxiada ON zhuankuanlaikuan.上级文号 = yusuanxiada.上级文号 AND zhuankuanlaikuan.上级文号> '0' LEFT OUTER JOIN (SELECT 上级文号, SUM(补助金额) AS C FROM yusuanxiada GROUP BY 上级文号) AS C ON zhuankuanlaikuan.上级文号 = C.上级文号 
问题主要出在红色的部分，绿色的部分昨天已经调试通过了，我估计的，不知道对不对？谢谢Herb2!

Answer 13

SELECT 
zhuankuanlaikuan.上级文号, 
zhuankuanlaikuan.来款项目, 
zhuankuanlaikuan.科目编码, 
zhuankuanlaikuan.科目名称, 
zhuankuanlaikuan.来款金额, 
zhuankuanlaikuan.资金分类, 
zhuankuanlaikuan.备注, 
yusuanxiada.指标文号, 
yusuanxiada.单位名称, 
yusuanxiada.补助内容, 
yusuanxiada.补助金额, 
            剩余专款＝case when 
exists (select  1  from  yusuanxiada  
where  上级文号=  yusuanxiada.上级文号 and　指标文号> yusuanxiada.指标文号) 
then null  else　　zhuankuanlaikuan.来款金额 - ISNULL(C.C, 0) AS 剩余专款 FROM zhuankuanlaikuan LEFT OUTER JOIN yusuanxiada xiada ON zhuankuanlaikuan.上级文号 = yusuanxiada.上级文号 AND zhuankuanlaikuan.上级文号> '0' LEFT OUTER JOIN (SELECT 上级文号, SUM(补助金额) AS C FROM yusuanxiada GROUP BY 上级文号) AS C ON zhuankuanlaikuan.上级文号 = C.上级文号 

Answer 14

谢谢Herb2!为了表示感谢，我想再给您追加分！

Answer 15

SELECT 
a.上级文号, 
a.来款项目, 
a.科目编码, 
a.科目名称, 
a.来款金额, 
a.资金分类, 
a.备注, 
b.指标文号, 
b.单位名称, 
b.补助内容, 
b.补助金额, 
剩余专款＝case when exists (select  1  from  yusuanxiada  where  上级文号=  b.上级文号 and　指标文号> b.指标文号) 
         then null  else a.来款金额 - ISNULL(C.C, 0)  FROM zhuankuanlaikuan  a LEFT OUTER JOIN yusuanxiada b ON a.上级文号 = b.上级文号 AND a.上级文号> '0' LEFT OUTER JOIN (SELECT 上级文号, SUM(补助金额) AS C FROM yusuanxiada GROUP BY 上级文号) AS C ON a.上级文号 = C.上级文号 

Answer 16

SELECT 
a.上级文号, 
a.来款项目, 
a.科目编码, 
a.科目名称, 
a.来款金额, 
a.资金分类, 
a.备注, 
b.指标文号, 
b.单位名称, 
b.补助内容, 
b.补助金额, 
剩余专款＝case when exists (select  1  from  yusuanxiada  where  上级文号=  b.上级文号 and　指标文号> b.指标文号) 
         then null  else a.来款金额 - ISNULL(C.C, 0)
FROM zhuankuanlaikuan  a 
     LEFT OUTER JOIN yusuanxiada b ON a.上级文号 = b.上级文号 AND a.上级文号> '0' 
     LEFT OUTER JOIN (SELECT 上级文号, SUM(补助金额) AS C FROM yusuanxiada GROUP BY 上级文号) AS C ON a.上级文号 = C.上级文号 

Answer 17

剩余专款＝case when exists (select  1  from  yusuanxiada  where  上级文号=  b.上级文号 and　指标文号> b.指标文号) 
         then null  else a.来款金额 - ISNULL(C.C, 0) end --这里漏了

Answer 18

谢谢Herb2，不过还是不行，我把提示信息的截图发给您，麻烦您再给指点一下！

Answer 19

剩余专款＝case when exists (select  1  from  yusuanxiada  where  上级文号=  b.上级文号 and　指标文号> b.指标文号) 
         then null  else a.来款金额 - ISNULL(C.C, 0)
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
下划线对应的位置有非法字符，估计是全角空格，删除后重新插入空格。

Answer 20

太不好意思了，代码问题通过了！我是在word里输的代码。代码没有问题了，但是余款的问题好像没有出现红色的部分，仍然是两个10000.00元。真是遗憾！次数太多了，我给您给贴吧，有点太麻烦您了！*结果--------------------------------------------------------------------------
A       B      C         D     E      F       G         余额                                       
-----  ----    ------- ----- -----   ----    --------- ---------------------------------------- 
111号  农业    80000.00 111号  001号  农业    10000.00   NULL111号  农业    80000.00 111号  002号  农业    60000.00   10000.00 
112号  林业    30000.00 112号  003号  林业    30000.00   .00 
113号  医疗    40000.00 NULL   NULL   NULL    NULL      40000.00 

Answer 21

剩余专款＝case when exists (select  1  from  yusuanxiada  where  上级文号=  b.上级文号 and　指标文号> b.指标文号) －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
这里的字段指标文号必须是在yusuanxiada表中相同的上级文号下能够区分的字段，我是根据你的例子数据选取的，你应该根据实际情况更换对应的字段。