求一sql(50分)

江

江西的delphi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-18

• #1

d5,ado,access2000
最好用一条sql,测试通过就发分
有客户表A AID(客户编号) ANAME(客户名称)
销售表B BID(销售编号) AID(客户编号)
应收款表C 自增ID BID(销售编号) fMoney(金额) dLimitDate(截止日期)
缴款表D 自增ID BID(销售编号) fMoney(金额) dDate(缴款日期)

最后统计出
BID(销售编号) ANAME(客户名称) fDealTotal(总应收款)
fPayTotal(总已缴款) fDealPayTotal(总到期未付)

注意:一客户有几个销售编号，有些销售表可能没有应收款和缴款记录，
一销售编号有几个应收款记录，
fDealPayTotal(总到期未付)是按当前日期统计(缴款表总金额-应收款总金额)。

 

Z

zhanzehua

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-18

• #2

select B.BID,ANAME,SUM(C.fMoney) fDealTotal,
SUM(D.fMoney) fPayTotal,SUM(D.fMoney) - SUM(C.fMoney) fDealPayTotal
from A, B, C, D
where A.AID = B.AID
and B.BID *= C.BID
and B.BID *= D.BIN
group by B.BID,ANAME

注意：数据库不同，外连接不同

 

江

江西的delphi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-19

• #3

TO:zhanzehua
您好！我写了如下，可以通过，但fDealPayTotal
(总到期未付)是按当前日期统计即(缴款表总金额-(应收款中dLimitDate(截止日期)
<当前日期的总金额))。统计不出。
SELECT A.sHouseSellID,A.sItemID,A.sBuildingID,A.sHouseID,
A.sClientID,A.sOperatorID,A.sAgentID,
SUM(B.fMoney) AS fDealTotal,
SUM(C.fMoney) AS fPayTotal,(fDealTotal-fPayTotal) AS fDutyPay
FROM HouseSell A
LEFT OUTER JOIN (Deal B LEFT OUTER JOIN Pay C ON
B.sHouseSDID=C.sHouseSDID)
ON A.sHouseSellID=B.sHouseSDID
GROUP BY A.sHouseSellID,A.sItemID,A.sBuildingID,A.sHouseID,
A.sClientID,A.sOperatorID,A.sAgentID

 

Z

zhanzehua

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-19

• #4

可以把D表连接两次
select B.BID,ANAME,SUM(C.fMoney) fDealTotal,
SUM(D.fMoney) fPayTotal,SUM([red]E[/red].fMoney) - SUM(C.fMoney) fDealPayTotal
from A, B, C, D,D E
where A.AID = B.AID
and B.BID *= C.BID
and B.BID *= D.BID
and B.BID *= E.BID
and E.dDate<= dLimitDate
group by B.BID,ANAME

这里只考虑日期与BID有关

 

江

江西的delphi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-19

• #5

to:zhanzehua
谢谢！我用如下通过，明天就给分。
SELECT A.sHouseSellID,A.sItemID,A.sBuildingID,A.sHouseID,A.sClientID,
A.sOperatorID,A.sAgentID,SUM(B.fMoney) AS fDealTotal,SUM(C.fMoney) AS
fPayTotal,(fDealTotal-fPayTotal) AS fDutyPay,(SUM(D.fMoney)-SUM(C.fMoney))
AS fDateNoPay
FROM HouseSell A LEFT OUTER JOIN (Deal B LEFT OUTER JOIN (Deal D LEFT OUTER
JOIN Pay C ON (D.sHouseSDID=C.sHouseSDID AND D.dLimitDate<NOW() )) ON
B.sHouseSDID=D.sHouseSDID ) ON (A.bIsAvail=-1 AND A.sHouseSellID=B.sHouseSDID)
GROUP BY A.sHouseSellID,A.sItemID,A.sBuildingID,A.sHouseID,A.sClientID
,A.sOperatorID,A.sAgentID

 

Z

zhanzehua

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-19

• #6

谢谢你的TOOLBAR97,不用了。以后有问题定会请教
email zhanzehua@163.net

 

江

江西的delphi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-19

• #7

to:zhanzehua,
你好！请帮我看一下。
由于表A(销售表)对应多个表B(应收表)和表C(缴款表)和表D(和表B同是应收表),所以用下面
sql统计出的款项会是对应记录数的倍数(表A-->表B)。
SELECT A.sHouseSellID,A.sItemID,SUM(IIF(B.fMoney<>NULL,B.fMoney,0)) AS
fDealTotal,SUM(IIF(C.fMoney<>NULL,C.fMoney,0)) AS fPayTotal,
(fDealTotal-fPayTotal) AS fDutyPay,SUM(IIF(D.fMoney<>NULL,D.fMoney,0))
-SUM(IIF(C.fMoney<>NULL,C.fMoney,0))) AS fDateNoPay FROM HouseSell
A LEFT OUTER JOIN (Deal B LEFT OUTER JOIN (Deal D LEFT OUTER JOIN
Pay C ON (D.sHouseSDID=C.sHouseSDID AND D.dLimitDate<NOW() ))
ON B.sHouseSDID=D.sHouseSDID ) ON (A.bIsAvail=-1 AND
A.sHouseSellID=B.
GROUP BY A.sHouseSellID,A.sItemID,A.sBuildingID,A.sHouseID,A.sClientID,A.sOperatorID,A.sAgentID ORDER BY A.sHouseSellID

 

Z

zhanzehua

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-20

• #8

原因应该出在B与D的连接上
B中sHouseSDID应该不是唯一的值，这样B,D的连接就会有交叉
D和C也一样
建议B,C,D均与A表连接
另外
如果只SELECT A.sHouseSellID,A.sItemID
那么 GROUP BY A.sHouseSellID,A.sItemID
就行了

 

江

江西的delphi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-20

• #9

原因应该出在B与D的连接上
B中sHouseSDID应该不是唯一的值，这样B,D的连接就会有交叉
D和C也一样
建议B,C,D均与A表连接
//但是没有D表,连接好象是正常(款项值不会是几倍关系),(表B和表D是同一表,)
BCD表分别怎么和表A连呢，请写一下。

另外
如果只SELECT A.sHouseSellID,A.sItemID
那么 GROUP BY A.sHouseSellID,A.sItemID
就行了
//这个我知道,谢谢。

 

Z

zhanzehua

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-20

• #10

A LEFT OUTER JOIN B ON()
LEFT OUTER JOIN C ON()
LEFT OUTER JOIN D ON()

 

江

江西的delphi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-20

• #11

A LEFT OUTER JOIN B ON()
LEFT OUTER JOIN C ON()
LEFT OUTER JOIN D ON()
//ACCESS不能这么用left outer join，要用欠套，如果都要与表A连接，那就不知怎么做了？

 

Z

zhanzehua

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-20

• #12

这样
B RIGHT JOIN
(C RIGHT JOIN
(D RIGHT JOIN A
ON D.ID = A.FID)
ON C.ID = A.ID)
ON B.ID = A.ID;
好了，自己研究吧

 

江

江西的delphi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-21

• #13

TO:zhanzehua,
按你的方法，下面sql在access通过， 但还是倍数关系，请指指教！
SELECT A.sHouseSellID, A.sItemID, A.sBuildingID, A.sHouseID, A.sClientID, A.sOperatorID, A.sAgentID, SUM(IIF(B.fMoney<>
NULL,B.fMoney,0)) AS fDealTotal, SUM(IIF(C.fMoney<>NULL,
C.fMoney,0)) AS fPayTotal, (fDealTotal-fPayTotal) AS fDutyPay, (SUM(IIF(D.fMoney<>NULL,D.fMoney,0))-SUM(IIF(C.fMoney
<>NULL,C.fMoney,0))) AS fDateNoPay
FROM Deal AS B RIGHT JOIN (Pay AS C RIGHT JOIN (Deal AS D RIGHT JOIN HouseSell AS A ON D.sHouseSDID=A.sHouseSellID
AND D.dLimitDate<NOW()) ON C.sHouseSDID=A.sHouseSellID) ON B.sHouseSDID=A.sHouseSellID
AND A.bIsAvail=-1
GROUP BY A.sHouseSellID, A.sItemID, A.sBuildingID, A.sHouseID, A.sClientID, A.sOperatorID, A.sAgentID;

 

江

江西的delphi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-22

• #14

to:zhanzehua
谢谢！
答案类似下面的：
select Sell.SellID as SellID,
Sell.HouseID as HouseID,
A.DealTotal as DealTotal,
B.PayTotal as PayTotal,
(DealTotal-PayTotal) as NoPay,
C.APay-PayTotal
from
((Sell left outer join
(select SellID,SUM(DealMoney) as DealTotal from Deal group by SellID) as A
on Sell.SellID=A.SellID)
left outer join
(select SellID,SUM(PayMoney) as PayTotal from Pay group by SellID) as B
on Sell.SellID=B.SellID)
left outer join
(select SellID,SUM(DealMoney) as APay from Deal where LimitDate<=Now() group by SellID) as C
on Sell.SellID=C.SellID