三个表的组合，如何查询出送货单的回款情况？

Question

现在我有三个表，一个是主表，另外两个是从表。主表的结构由三个字段“BillCode（单号）/Billdate（单据日期）/Depot（仓库）”组成；第一个从表由三个字段”BillCode（单号）/Number（数量）/Price（单价）“组成；第二个从表由三个字段”BillCode（单号）/ObjBillCode（业务单号）/TotalMoney（总金额，由第一个从表的明细项目的SUM(Number*Price)获得）/BcMoney（本次收款金额）“组成。第一，第二个主从表组成送货单；第一，第三个主从表组成收款单。注意：送货单的单号跟收款单的单号是相互独立的，三个表的关联关系是"表1.BillCode=表2.BillCode=表3.ObjBillCode”。问题是：如何在不使用临时表的情况下，使用一条查询语句，查出结构如“BillCode/TotalMoney/SUM（BcMoney）”的结果？实际上就是根据送货单，查询出送货单的回款情况。

Answer 1

收款只与发货有关,与主表和订货都没关系:
select ObjbillCode,TotalMoney,sum(BcMoney) from 第二个从表 group by ObjbillCode,TotalMoney

Answer 2

虽然没有收到款,但发货单应该是有的,只是上面的 BcMoney=0 而已.

Answer 3

从来没有收过款的发货单，在第二个从表中是不会出现的。只有单一个发货单收到了至少一次货款，这才会制作一张回款单，然后在汇款单中的ObjbillCode提及到发货单。也就是，没有收款纪录的发货单，几乎永远不做收款单的。

Answer 4

您可能没有看明白。第二个从表记录的是收款的情况，第一个从表才是发货的记录。“发货,那是一定得有发货单,至于你收没收到钱,反正我是发了”这句话是正确的，我们这里只有记录发货单的回款情况，并没有预收货款的概念的。当第一个从表记录的发货记录没有用收到任何货款，第二个从表中的ObjbillCode是不会提到那个发货单的。发货单的编号体系是“FH-2011-09-04-0001”，而收款单的编号体系是“SK-2011-09-06-0003”他们的表体都存放在主表里，但是他们的编码都不一样的。也许“FH-2011-09-04-0001”与“SK-2011-09-06-0003”是针对同一个发货业务编制的两张单据，也许“SK-2011-09-06-0003”对应着“FH-2011-09-04-0001”及“FH-2011-09-04-0002”两张送货单据。

Answer 5

select a.billCode,sum(a.Number+a.Price),sum(isnull(b.BcMoney,0)) 
from 第一个从表 a left join 第二个从表 b on a.BillCode=b.ObjBillCode
group by a.billCode

Answer 6

select ObjbillCode,TotalMoney,sum(BcMoney) from 第二个从表 group by ObjbillCode,TotalMoney
您这个代码也很有启发性，如果只统计有回款的项目，这个是很简洁的代码的。只是现在的情况比较麻烦，有些送货单从来没有汇过款，也需要列举出来。

Answer 7

这个能解决，我需要过滤主表中，depot=“第一成品仓”，或者制单日期为“2011-09-01”到“2011-09-30”吗？我上面的提问是为了简洁，实质上，主表需要提供条件过滤的。

Answer 8

哥们,你不能一次说完所有需求啊.............select a.billCode,sum(a.Number+a.Price),sum(isnull(b.BcMoney,0)) 
from 第一个从表 a left join 第二个从表 b on a.BillCode=b.ObjBillCode
where exists(select 1 from 主表 where billCode=a.billCode and depot='第一成品仓' and 制单日期 between '2011-08-01' and '2011-09-30')
group by a.billCode

Answer 9

{  SQLStr := 'select *,TInNumber-TOutNumber as THjNumber,TInMoney-TOutMoney as THjMoney from('+
            'select a.CoutName,'+
            'sum(iif( a.Identifier=''采购进货'',b.TNumber,0)) as TInNumber,'+
            'sum(iif( a.Identifier=''采购进货'',b.Price*b.TNumber,0)) as TInMoney,'+
            'sum(iif( a.Identifier=''采购退货'',b.TNumber,0)) as TOutNumber,'+
            'sum(iif( a.Identifier=''采购退货'',b.Price*b.TNumber,0)) as TOutMoney '+
            'from T_Bill a inner join T_BillDetail b on a.BillCode=b.BillCode '+
            'where addDate between :StartDate and :EndDate ';   }  SQLStr := 'select a.billCode,sum(a.Number*a.Price) as TotalMoney,sum(isnull(b.BcMoney,0)) as FinishMoney '+
            ' from T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode '+
            ' where exists(select 1 from T_Bill where billCode=a.billCode '+
            ' and addDate between :StartDate and :EndDate ';  if cbbDepot.Text <>'' then SQLStr := SQLStr +
   ' and a.CoutName like '+ Quotedstr('%'+cbbDepot.Text+'%');  if cbbSalesPurchase.Text <>'' then SQLStr := SQLStr +
   ' and a.SalesPurchase like '+ Quotedstr('%'+cbbSalesPurchase.Text+'%');  if cbbBillUser.Text <>'' then SQLStr := SQLStr +
   ' and a.BillUser like '+ Quotedstr('%'+cbbBillUser.Text+'%');  SQLStr := SQLStr +')group by a.billCode';  with qrySkYsZkHzBill do
  begin
    Close;
    SQL.Clear;
    SQL.Add(SQLStr);
    Parameters.ParamByName('StartDate').Value:=edtBeginBill.Value;
    Parameters.ParamByName('EndDate').Value:=edtEndBill.Value;
    Open;
  end;
提示用“于函数参数的个数不对 在查询表达式sum(isnull(b.BcMoney,0))中 ”

Answer 10

TotalMoney：总金额
FinishMoney：已经完成的金额
T_Bill：送货退货单表头，兼任收款付款单表头
T_BillDetail：送货退货单表体
T_SFBillDetail：收款付款单表体

Answer 11

sum(isnull(b.BcMoney,0))
这个不会错的.

Answer 12

{  SQLStr := 'select *,TInNumber-TOutNumber as THjNumber,TInMoney-TOutMoney as THjMoney from('+
            'select a.CoutName,'+
            'sum(iif( a.Identifier=''采购进货'',b.TNumber,0)) as TInNumber,'+
            'sum(iif( a.Identifier=''采购进货'',b.Price*b.TNumber,0)) as TInMoney,'+
            'sum(iif( a.Identifier=''采购退货'',b.TNumber,0)) as TOutNumber,'+
            'sum(iif( a.Identifier=''采购退货'',b.Price*b.TNumber,0)) as TOutMoney '+
            'from T_Bill a inner join T_BillDetail b on a.BillCode=b.BillCode '+
            'where addDate between :StartDate and :EndDate ';   }  SQLStr := 'select a.billCode,sum(a.Number*a.Price) as TotalMoney '+    //,sum(isnull(b.BcMoney,0)) as FinishMoney
            ' from T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode '+
            ' where exists(select 1 from T_Bill c where c.billCode=a.billCode '+
            ' and c.addDate between :StartDate and :EndDate ';  if cbbDepot.Text <>'' then SQLStr := SQLStr +
   ' and c.CoutName like '+ Quotedstr('%'+cbbDepot.Text+'%');  if cbbSalesPurchase.Text <>'' then SQLStr := SQLStr +
   ' and c.SalesPurchase like '+ Quotedstr('%'+cbbSalesPurchase.Text+'%');  if cbbBillUser.Text <>'' then SQLStr := SQLStr +
   ' and c.BillUser like '+ Quotedstr('%'+cbbBillUser.Text+'%');  SQLStr := SQLStr +')group by a.billCode';  with qrySkYsZkHzBill do
  begin
    Close;
    SQL.Clear;
    SQL.Add(SQLStr);
    Parameters.ParamByName('StartDate').Value:=edtBeginBill.Value;
    Parameters.ParamByName('EndDate').Value:=edtEndBill.Value;
    Open;
  end;这样改，那么就提示“至少一个参数没有被指定值”，我是Access数据库，有关系吗？

Answer 13

  SQLStr := 'select a.billCode,sum(a.TNumber*a.Price) as TotalMoney '+    //,sum(isnull(b.BcMoney,0)) as FinishMoney
            ' from T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode '+
            ' where exists(select 1 from T_Bill c where c.billCode=a.billCode '+
            ' and c.addDate between :StartDate and :EndDate ';  if cbbDepot.Text <>'' then SQLStr := SQLStr +
   ' and c.CoutName like '+ Quotedstr('%'+cbbDepot.Text+'%');  if cbbSalesPurchase.Text <>'' then SQLStr := SQLStr +
   ' and c.SalesPurchase like '+ Quotedstr('%'+cbbSalesPurchase.Text+'%');  if cbbBillUser.Text <>'' then SQLStr := SQLStr +
   ' and c.BillUser like '+ Quotedstr('%'+cbbBillUser.Text+'%');  SQLStr := SQLStr +')group by a.billCode';  with qrySkYsZkHzBill do
  begin
    Close;
    SQL.Clear;
    SQL.Add(SQLStr);
    Parameters.ParamByName('StartDate').Value:=edtBeginBill.Value;
    Parameters.ParamByName('EndDate').Value:=edtEndBill.Value;
    Open;
  end;这个写法终于可以查询出结果了，目前剩下两个问题，一个是结果中没有主表的日期参数，也没有已经收款的金额。。
直接改下面的，不能查到发货单的日期的：  SQLStr := 'select c.,addDate,a.billCode,sum(a.TNumber*a.Price) as TotalMoney '+    //,sum(isnull(b.BcMoney,0)) as FinishMoney
            ' from T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode '+
            ' where exists(select 1 from T_Bill c where c.billCode=a.billCode '+
            ' and c.addDate between :StartDate and :EndDate ';  if cbbDepot.Text <>'' then SQLStr := SQLStr +
   ' and c.CoutName like '+ Quotedstr('%'+cbbDepot.Text+'%');  if cbbSalesPurchase.Text <>'' then SQLStr := SQLStr +
   ' and c.SalesPurchase like '+ Quotedstr('%'+cbbSalesPurchase.Text+'%');  if cbbBillUser.Text <>'' then SQLStr := SQLStr +
   ' and c.BillUser like '+ Quotedstr('%'+cbbBillUser.Text+'%');  SQLStr := SQLStr +')group by a.billCode';  with qrySkYsZkHzBill do
  begin
    Close;
    SQL.Clear;
    SQL.Add(SQLStr);
    Parameters.ParamByName('StartDate').Value:=edtBeginBill.Value;
    Parameters.ParamByName('EndDate').Value:=edtEndBill.Value;
    Open;
  end;

Answer 14

在ACCESS里,SQL中的isnull函数由NZ()函数代替,形式是:
sum(NZ(b.BcMoney,0))

Answer 15

提示NZ函数未定义
我使用下面的语句可以查询，但是查询出来的结果不对：  SQLStr := 'select a.billCode,sum(a.TNumber*a.Price) as TotalMoney,'+
            ' sum(iif(isnull(b.BcMoney),b.BcMoney,0)) as FinishMoney,'+
            ' sum(a.TNumber*a.Price) - sum(iif(isnull(b.BcMoney),b.BcMoney,0)) as NotFinishMoney'+    //,sum(isnull(b.BcMoney,0)) as FinishMoney
            ' from T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode '+
            ' where exists(select 1 from T_Bill c where c.billCode=a.billCode '+
            ' and c.addDate between :StartDate and :EndDate ';上面的结构，FinishMoney应该是有数据的，但是结果是零，而那个减法，不计算结果。
我QQ是36519719，能加我吗？

Answer 16

你的NZ用法不对.
把下面这句拷到ACCESS的查询SQL界面去运行一下试试:
select a.billCode,sum(a.TNumber*a.Price) as TotalMoney,
sum(nz(b.BcMoney,0)) as FinishMoney,
sum(a.TNumber*a.Price) - sum(nz(b.BcMoney,0)) as NotFinishMoney,
sum(nz(b.BcMoney,0)) as FinishMoney
from T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode
where exists(select 1 from T_Bill c where c.billCode=a.billCode
and c.addDate between "2011-09-01" and "2011-09-30"

Answer 17

最后还差个右括号.
select a.billCode,sum(a.TNumber*a.Price) as TotalMoney,
sum(nz(b.BcMoney,0)) as FinishMoney,
sum(a.TNumber*a.Price) - sum(nz(b.BcMoney,0)) as NotFinishMoney,
sum(nz(b.BcMoney,0)) as FinishMoney
from T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode
where exists(select 1 from T_Bill c where c.billCode=a.billCode
and c.addDate between "2011-09-01" and "2011-09-30")

Answer 18

  SQLStr := 'select a.billCode,sum(a.TNumber*a.Price) as TotalMoney,'+
            ' sum(nz(b.BcMoney,0)) as FinishMoney,'+
            ' sum(a.TNumber*a.Price) - sum(nz(b.BcMoney,0)) as NotFinishMoney '+
            ' from T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode ' +
            ' where exists(select 1 from T_Bill c where c.billCode=a.billCode ' +
            ' and c.addDate between "2011-09-01" and "2011-09-30" ';  if cbbDepot.Text <>'' then SQLStr := SQLStr +
   ' and c.CoutName like '+ Quotedstr('%'+cbbDepot.Text+'%');  if cbbSalesPurchase.Text <>'' then SQLStr := SQLStr +
   ' and c.SalesPurchase like '+ Quotedstr('%'+cbbSalesPurchase.Text+'%');  if cbbBillUser.Text <>'' then SQLStr := SQLStr +
   ' and c.BillUser like '+ Quotedstr('%'+cbbBillUser.Text+'%');  SQLStr := SQLStr +') group by a.billCode';  with qrySkYsZkHzBill do
  begin
    Close;
    SQL.Clear;
    SQL.Add(SQLStr);
    //Parameters.ParamByName('StartDate').Value:=edtBeginBill.Value;
    //Parameters.ParamByName('EndDate').Value:=edtEndBill.Value;
    Open;
  end;仍然提示没有定义的函数NZ
  SQLStr := 'select a.billCode,sum(a.TNumber*a.Price) as TotalMoney,'+
            ' sum(iif(isnull(b.BcMoney),b.BcMoney,0)) as FinishMoney,'+
            ' sum(a.TNumber*a.Price) - sum(iif(isnull(b.BcMoney),b.BcMoney,0)) as NotFinishMoney'+    //,sum(isnull(b.BcMoney,0)) as FinishMoney
            ' from T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode '+
            ' where exists(select 1 from T_Bill c where c.billCode=a.billCode '+
            ' and c.addDate between :StartDate and :EndDate ';这组代码能查到结果，就是结果不对。FinishMoney有值得是0，null是null

Answer 19

这里还有一个问题，使用了exists后，只返回符合条件的第一个从表的记录，但是这个结果并不包括主表的项目，主表中最基本的单据日期（发货日期）是需要显示的。

Answer 20

SELECT a.BillCode, SUM(a.TNumber * a.Price) AS TotalMoney, SUM(ISNULL(b.BcMoney, 0)) 
      AS FinishMoney, SUM(a.TNumber * a.Price) - SUM(ISNULL(b.BcMoney, 0)) 
      AS NotFinishMoney
FROM T_BillDetail a LEFT OUTER JOIN
      T_SFBillDetail b ON a.BillCode = b.ObjBillCode
WHERE EXISTS
          (SELECT 1
         FROM T_Bill c
         WHERE c.billCode = a.billCode AND c.addDate BETWEEN '2011 - 08 - 01' AND 
               '2011 - 09 - 30')
GROUP BY a.BillCode我刚才特意到MSSQL里测试，确实是可以用的代码！就差没有addDatel了！！！

Answer 21

你可以在ACCESS的查询窗口测试你的SQL语句的,新建查询-->不选表-->在添加表的界面上右击-->SQL视图
可以在这里看你的SQL语句是否正确,是否能查出正确的结果.

Answer 22

我不知道ACCESS是否支持 exists.

Answer 23

oh my god！
是我搞错了，ACCESS支持exists.
正确的语句是  SQLStr := 'select a.billCode,sum(a.TNumber*a.Price) as TotalMoney,'+
            ' sum(iif(isnull(b.BcMoney),0,b.BcMoney)) as FinishMoney,'+
            ' sum(a.TNumber*a.Price) - sum(iif(isnull(b.BcMoney),0,b.BcMoney)) as NotFinishMoney'+    //,sum(isnull(b.BcMoney,0)) as FinishMoney
            ' from T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode '+
            ' where exists(select 1 from T_Bill c where c.billCode=a.billCode '+
            ' and c.addDate between :StartDate and :EndDate ';
  if cbbDepot.Text <>'' then SQLStr := SQLStr +
   ' and c.CoutName like '+ Quotedstr('%'+cbbDepot.Text+'%');  if cbbSalesPurchase.Text <>'' then SQLStr := SQLStr +
   ' and c.SalesPurchase like '+ Quotedstr('%'+cbbSalesPurchase.Text+'%');  if cbbBillUser.Text <>'' then SQLStr := SQLStr +
   ' and c.BillUser like '+ Quotedstr('%'+cbbBillUser.Text+'%');  SQLStr := SQLStr +') group by a.billCode';  with qrySkYsZkHzBill do
  begin
    Close;
    SQL.Clear;
    SQL.Add(SQLStr);
    Parameters.ParamByName('StartDate').Value:=edtBeginBill.Value;
    Parameters.ParamByName('EndDate').Value:=edtEndBill.Value;
    Open;
  end;sum(iif(isnull(b.BcMoney),0,b.BcMoney)) 刚才的写法，把为空值的情况写反了！！！！
现在就差让结果显示C.AddDate，老大再帮帮忙噻！

Answer 24

根据需要显示c.addDate 的需求，exists函数是不是不能使用呢？

Answer 25

大家帮帮忙啊！~~如何让结果显示c.addDate?就差一个问题点了！！

Answer 26

再连接一个主表就行了.SELECT a.BillCode,c.addDate, SUM(a.TNumber * a.Price ...
from ...
left join 主表 on a.billCode=c.billCode
...
group by a.billcode,c.adddate

Answer 27

我也猜是要连接一个主表，今天试了一天也没有试出来正确的写法。囧！
到底是如何结合下面原有的代码的？麻烦您最说明白一点，我先谢谢您了！  SQLStr := 'select a.billCode,sum(a.TNumber*a.Price) as TotalMoney,'+
            ' sum(iif(isnull(b.BcMoney),0,b.BcMoney)) as FinishMoney,'+
            ' sum(a.TNumber*a.Price) - sum(iif(isnull(b.BcMoney),0,b.BcMoney)) as NotFinishMoney'+    //,sum(isnull(b.BcMoney,0)) as FinishMoney
            ' from T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode '+
            ' where exists(select 1 from T_Bill c where c.billCode=a.billCode '+
            ' and c.addDate between :StartDate and :EndDate ';
  if cbbDepot.Text <>'' then SQLStr := SQLStr +
   ' and c.CoutName like '+ Quotedstr('%'+cbbDepot.Text+'%');  if cbbSalesPurchase.Text <>'' then SQLStr := SQLStr +
   ' and c.SalesPurchase like '+ Quotedstr('%'+cbbSalesPurchase.Text+'%');  if cbbBillUser.Text <>'' then SQLStr := SQLStr +
   ' and c.BillUser like '+ Quotedstr('%'+cbbBillUser.Text+'%');  SQLStr := SQLStr +') group by a.billCode';  with qrySkYsZkHzBill do
  begin
    Close;
    SQL.Clear;
    SQL.Add(SQLStr);
    Parameters.ParamByName('StartDate').Value:=edtBeginBill.Value;
    Parameters.ParamByName('EndDate').Value:=edtEndBill.Value;
    Open;
  end;

Answer 28

最终符合结果的查询：{ 重要的代码备份，一下的代码是只返回符合条件的结果
  SQLStr := 'select a.billCode,sum(a.TNumber*a.Price) as TotalMoney,'+
            ' sum(iif(isnull(b.BcMoney),0,b.BcMoney)) as FinishMoney,'+
            ' sum(a.TNumber*a.Price) - sum(iif(isnull(b.BcMoney),0,b.BcMoney)) as NotFinishMoney'+    //,sum(isnull(b.BcMoney,0)) as FinishMoney
            ' from T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode '+
            ' where exists(select 1 from T_Bill c where c.billCode=a.billCode '+
            ' and c.addDate between :StartDate and :EndDate ';   }  SQLStr := 'select a.billCode,c.addDate,sum(a.TNumber*a.Price) as TotalMoney,'+
            ' sum(iif(isnull(b.BcMoney),0,b.BcMoney)) as FinishMoney,'+
            ' sum(a.TNumber*a.Price) - sum(iif(isnull(b.BcMoney),0,b.BcMoney)) as NotFinishMoney'+    //,sum(isnull(b.BcMoney,0)) as FinishMoney
            ' from (T_BillDetail a left join T_SFBillDetail b on a.BillCode=b.ObjBillCode) '+
            ' left join T_Bill c on a.BillCode=c.BillCode '+
            ' where c.addDate between :StartDate and :EndDate ';  if cbbBillType.Text ='销售发货' then SQLStr := SQLStr + ' and c.Identifier=''销售发货'' ';
  if cbbBillType.Text ='销售退货' then SQLStr := SQLStr + ' and c.Identifier=''销售退货'' ';
  if cbbBillType.Text ='发货退货' then SQLStr := SQLStr + ' and (c.Identifier=''销售发货'' or c.Identifier=''销售退货'') ' ;  if cbbDepot.Text <>'' then SQLStr := SQLStr +
   ' and c.Depot like '+ Quotedstr('%'+cbbDepot.Text+'%');  if cbbSalesPurchase.Text <>'' then SQLStr := SQLStr +
   ' and c.SalesPurchase like '+ Quotedstr('%'+cbbSalesPurchase.Text+'%');  if cbbBillUser.Text <>'' then SQLStr := SQLStr +
   ' and c.BillUser like '+ Quotedstr('%'+cbbBillUser.Text+'%');  SQLStr := SQLStr +' group by a.billCode,c.addDate';  with qrySkYsZkHzBill do
  begin
    Close;
    SQL.Clear;
    SQL.Add(SQLStr);
    Parameters.ParamByName('StartDate').Value:=edtBeginBill.Value;
    Parameters.ParamByName('EndDate').Value:=edtEndBill.Value;
    Open;
  end;