问题:
有客户表, 业务表1,业务表2,业务表3,业务表4,业务表5(五种不同业务)业务表1有人民币,港币,美元
业务表2有人民币
业务表3有人民币,港币
业务表4有人民币,港币,美元业务表5有两个栏位,记录币种(人民币、港币、美元)以及金额。
(各种业务的金额是:业务1+业务2+业务3+业务4 - 业务5),即前4种是欠款,最后一种是还款,如何统计出该客户在某段时段之间:人民币、港币、美元 放到一个query里。delphi + access 2000
有客户表, 业务表1,业务表2,业务表3,业务表4,业务表5(五种不同业务)业务表1有人民币,港币,美元
业务表2有人民币
业务表3有人民币,港币
业务表4有人民币,港币,美元业务表5有两个栏位,记录币种(人民币、港币、美元)以及金额。
(各种业务的金额是:业务1+业务2+业务3+业务4 - 业务5),即前4种是欠款,最后一种是还款,如何统计出该客户在某段时段之间:人民币、港币、美元 放到一个query里。delphi + access 2000
RMB 100.00
HK 60.00
USD 120.00
HK 80.00
RMB 150.00做一个交叉查询,最后的输入是这样的:
RMB HK USD
250.00 140.00 120.00该语句如何写?
sum(case rmb when 数额 else 0 end ) RMB
sum(case HK when 数额 else 0 end ) HK
sum(case USD when 数额 else 0 end ) USD
from 业务表5
(sql server2000 通过)
(select RMB from 业务表2 where 客户ID = a.客户ID) +
(select RMB from 业务表3 where 客户ID = a.客户ID) +
(select RMB from 业务表4 where 客户ID = a.客户ID) -
(select RMB from 业务表5 where 客户ID = a.客户ID and 币种 = RMB) as RMB,
(select HKD from 业务表1 where 客户ID = a.客户ID) +
(select HKD from 业务表3 where 客户ID = a.客户ID) +
(select HKD from 业务表4 where 客户ID = a.客户ID) -
(select HKD from 业务表5 where 客户ID = a.客户ID and 币种 = HKD) as HKD,
(select USD from 业务表1 where 客户ID = a.客户ID) +
(select USD from 业务表4 where 客户ID = a.客户ID) -
(select USD from 业务表5 where 客户ID = a.客户ID and 币种 = USD) as USD
from 客户表(前提是业务表中每个客户只有一条记录,如果不是则先做统计,OK)
或许可以简化,自己来吧。
(select sum(RMB) from 业务表2 where 客户ID = a.客户ID) +
(select sum(RMB) from 业务表3 where 客户ID = a.客户ID) +
(select sum(RMB) from 业务表4 where 客户ID = a.客户ID) -
(select sum(RMB) from 业务表5 where 客户ID = a.客户ID and 币种 = RMB) as RMB,
(select sum(HKD) from 业务表1 where 客户ID = a.客户ID) +
(select sum(HKD) from 业务表3 where 客户ID = a.客户ID) +
(select sum(HKD) from 业务表4 where 客户ID = a.客户ID) -
(select sum(HKD) from 业务表5 where 客户ID = a.客户ID and 币种 = HKD) as HKD,
(select sum(USD) from 业务表1 where 客户ID = a.客户ID) +
(select sum(USD) from 业务表4 where 客户ID = a.客户ID) -
(select sum(USD) from 业务表5 where 客户ID = a.客户ID and 币种 = USD) as USD
from 客户表再次说明,我使用的是 delphi 6 + access2000。而非 sql server 2000。