另外两个贴子为:http://expert.csdn.net/Expert/topic/1628/1628622.xml?temp=.6471063
http://expert.csdn.net/Expert/topic/1634/1634356.xml?temp=.1038477现在我把以前的问题和新出现的问题再重新整理一下:(不好意思,因为我刚刚学delphi。。又比较急。麻烦大家了)系统采用工具:delphi+access这里有三张表,三张之间以bianhao(编号)相连:table1---主表:bianhao(编号)
name(姓名)
shangqiyue(上期余额)
tingjitime(停机时间)table2---应收费用
bianhao(编号)
yuefen(月份)
feiyong1(费用1)
feiyong2(费用2)
otherfeiyong(其他费用)其中各月的:feiyong1(费用1)+feiyong2(费用2)+otherfeiyong(其他费用)=本期已收金额
table3----已收费用 (本期发生的所有的费用全部放在一个表中)bianhao(编号)
shoufeitime(收费时间)
shoufeijine(收费金额)其中:所有的收费金额之和为本期应收金额:
最后这三张表连接起来又形成了一个公式:shangqiyue(上期余额)+本期已收金额-本期应收金额=当前余额(若当前余额<0就表示欠费)
这时候,我想在一个窗口的DBgrid控件里把当前table1中卡号情况都统计出来,应该怎么做?我举个实例吧:例如
table1中有记录如下:(13611111111 上期有余额200)bianhao name shangqiyue tingjitime13611111111 小娇 200
13622222222 老王 300table2中记录如下: (13611111111 本期各月应收费用之和为1500+1800+900=4200) bianhao yuefen feiyong1 feiyong2 otherfei 13611111111 1 500 500 500 (总和 ---1500)
13611111111 2 600 600 600 (总和---1800)
13611111111 3 300 300 300 (总和--900)
13622222222 1 400 400 400 (总和--1200)
table3记录如下: (13611111111 本期的交费总和为 1500+1800+600= 3900) bianhao shoufeitime shoufeijine13611111111 2003-2-15 1500
13611111111 2003-3-15 1800
13611111111 2003-4-15 600
13622222222 2003-4-15 1000 (多交的为预交)
这时候我需要查出table1中所有卡号的统计情况:13611111111的当前余额=3900+200-4200=-100 表示这个号欠费。
13622222222的当前余额=1000+300-1200=100 表示这个号有余额100。 这个时候我需要在DBGrid显示出来的信息应该是:bianhao shangqiyue(上期余额) yingshou(本期应收) yishou(本期已收) jiyu(当前余额)13611111111 200 4200 3900 -100
13622222222 300 1200 1000 100 我应该怎么做?在各位朋友的帮助下,问题基本得到了解决了。我采用的是 chenquan(嘉威王子) 的方法:代码如下:select distinct bianhao,a.fee 上期余额,b.fee 本期应收
,c.fee 本期已收,A.FEE+c.fee-b.fee 当前余额
from
(select bianhao,shangqiyue as fee from table1) a,
(select bianhao,sum(feiyong1+feiyong2+ortherfei) as fee from table2
group by bianhao) b, --4200
(select bianhao,sum(shoufeijine) as fee from table3
group by bianhao) c --3900
where a.bianhao=b.bianhao and b.bianhao=c.bianhao但这段代码有个缺陷,就是说当13611111111只在table1中有记录,而在table2和table3中无记录时,就把这个卡号的情况统计出来。。我需要的是:把所有卡号的情况都统计出来。应该怎么修改这段代码呢?
http://expert.csdn.net/Expert/topic/1634/1634356.xml?temp=.1038477现在我把以前的问题和新出现的问题再重新整理一下:(不好意思,因为我刚刚学delphi。。又比较急。麻烦大家了)系统采用工具:delphi+access这里有三张表,三张之间以bianhao(编号)相连:table1---主表:bianhao(编号)
name(姓名)
shangqiyue(上期余额)
tingjitime(停机时间)table2---应收费用
bianhao(编号)
yuefen(月份)
feiyong1(费用1)
feiyong2(费用2)
otherfeiyong(其他费用)其中各月的:feiyong1(费用1)+feiyong2(费用2)+otherfeiyong(其他费用)=本期已收金额
table3----已收费用 (本期发生的所有的费用全部放在一个表中)bianhao(编号)
shoufeitime(收费时间)
shoufeijine(收费金额)其中:所有的收费金额之和为本期应收金额:
最后这三张表连接起来又形成了一个公式:shangqiyue(上期余额)+本期已收金额-本期应收金额=当前余额(若当前余额<0就表示欠费)
这时候,我想在一个窗口的DBgrid控件里把当前table1中卡号情况都统计出来,应该怎么做?我举个实例吧:例如
table1中有记录如下:(13611111111 上期有余额200)bianhao name shangqiyue tingjitime13611111111 小娇 200
13622222222 老王 300table2中记录如下: (13611111111 本期各月应收费用之和为1500+1800+900=4200) bianhao yuefen feiyong1 feiyong2 otherfei 13611111111 1 500 500 500 (总和 ---1500)
13611111111 2 600 600 600 (总和---1800)
13611111111 3 300 300 300 (总和--900)
13622222222 1 400 400 400 (总和--1200)
table3记录如下: (13611111111 本期的交费总和为 1500+1800+600= 3900) bianhao shoufeitime shoufeijine13611111111 2003-2-15 1500
13611111111 2003-3-15 1800
13611111111 2003-4-15 600
13622222222 2003-4-15 1000 (多交的为预交)
这时候我需要查出table1中所有卡号的统计情况:13611111111的当前余额=3900+200-4200=-100 表示这个号欠费。
13622222222的当前余额=1000+300-1200=100 表示这个号有余额100。 这个时候我需要在DBGrid显示出来的信息应该是:bianhao shangqiyue(上期余额) yingshou(本期应收) yishou(本期已收) jiyu(当前余额)13611111111 200 4200 3900 -100
13622222222 300 1200 1000 100 我应该怎么做?在各位朋友的帮助下,问题基本得到了解决了。我采用的是 chenquan(嘉威王子) 的方法:代码如下:select distinct bianhao,a.fee 上期余额,b.fee 本期应收
,c.fee 本期已收,A.FEE+c.fee-b.fee 当前余额
from
(select bianhao,shangqiyue as fee from table1) a,
(select bianhao,sum(feiyong1+feiyong2+ortherfei) as fee from table2
group by bianhao) b, --4200
(select bianhao,sum(shoufeijine) as fee from table3
group by bianhao) c --3900
where a.bianhao=b.bianhao and b.bianhao=c.bianhao但这段代码有个缺陷,就是说当13611111111只在table1中有记录,而在table2和table3中无记录时,就把这个卡号的情况统计出来。。我需要的是:把所有卡号的情况都统计出来。应该怎么修改这段代码呢?
where a Left outer JOIN
b ON a.bianhao = b.bianhao
and b Left outer JOIN
c ON b.bianhao = c.bianhao
table1中有的 。 table2/table3中不一定有。
select table1.bianhao,table1.shangqiyue,sum(feiyong1+feiyong2+otherfee) allfee,sum(table3.shoufeejine) as yijiaofee,
(case when sum(table2.shangqiyue) is null then 0 else sum(table2.shangqiyue) end)+(case when sum(table3.shoufeejine) is null then 0 else sum(table3.shoufeejine) end)-(case when sum(feiyong1+feiyong2+otherfee) is null then 0 else sum(feiyong1+feiyong2+otherfee) end) jieyu from table1 left outer join table2 on table1.bianhao=table2.banhao left outer join table3 on table1.bianhao=table3.bianhao where
(case when sum(table2.shangqiyue) is null then 0 else sum(table2.shangqiyue) end)+(case when sum(table3.shoufeejine) is null then 0 else sum(table3.shoufeejine) end)-(case when sum(feiyong1+feiyong2+otherfee) is null then 0 else sum(feiyong1+feiyong2+otherfee) end)<0 group by table1.bianhao,shangqiyue
FROM (table1 Left JOIN table2 ON table1.bianhao = table2.bianhao) Left JOIN table3 ON table1.bianhao = table3.bianhao
GROUP BY table1.bianhao, table1.shangqiyue;
(case when sum(table2.shangqiyue) is null then 0 else sum(table2.shangqiyue) shangqiyue是属于table1的。。好的。。我试一下。好像是昨晚也试过了。。当时不行。。能在chenquan(嘉威王子) 的代码的基础上修改吗?
这个100%正确
SELECT table1.bianhao, table1.shangqiyue AS 上期余额, a.本期应收,b.本期已收,上期余额+本期已收-本期应收 AS 当前余额 from
(select table1.bianhao,sum(table2.feiyong1+table2.feiyong2+table2.otherfeiyong) AS 本期应收 from table1 LEFT JOIN table2 ON table1.bianhao=table2.bianhao group by table1.bianhao) a,
(select table1.bianhao,sum(table3.shoufeijine) AS 本期已收 from table1 LEFT JOIN table3 ON table1.bianhao=table3.bianhao group by table1.bianhao ) b,table1
where table1.bianhao = a.bianhao and table1.bianhao = b.bianhao
select distinct a.bianhao,
a.shangqiyue,
b.yingshou,
c.yishou,
(c.yishou-b.yingshou) as jiyu
from table1 a,
(select bianhao, sum(feiyong1+feiyong2+otherfeiyong) as yingshou from table2 group by table2.bianhao) b,
(select bianhao, sum(shoufeijine) as yishou from table3 group by bianhao) c
where a.bianhao=b.bianhao and a.bianhao=c.bianhao
group by a.bianhao, a.shangqiyue, b.yingshou, c.yishou
order by a.bianhao
select table1.bianhao,table1.shangqiyue,sum(feiyong1+feiyong2+otherfee) allfee,sum(table3.shoufeejine) as yijiaofee,
table1.shangqiyue+sum(table3.shoufeejine)-sum(feiyong1+feiyong2+otherfee) as jieyu from table1 left outer join table2 on table1.bianhao=table2.bianhao left outer join table3 on table1.bianhao=table3.bianhao where
table1.shangqiyue+sum(table3.shoufeejine)-sum(feiyong1+feiyong2+otherfee)<0 group by table1.bianhao,shangqiyue
又想起来了,不加case如果有的表编号不存在的话条件不成立还是统计不出来!
那你先把where
table1.shangqiyue+sum(table3.shoufeejine)-sum(feiyong1+feiyong2+otherfee)<0去掉先试试!
with a as (select table1.bianhao,sum(table2.feiyong1+table2.feiyong2+table2.otherfeiyong) AS bqyse from table1 LEFT outer JOIN table2 ON table1.bianhao=table2.bianhao group by table1.bianhao) ,b as (select table1.bianhao,sum(table3.shoufeijine) AS yishoue from table1 LEFT OUter JOIN table3 ON table1.bianhao=table3.bianhao group by table1.bianhao )
select table1.bianhao,table1.shangqiyue,case when a.bqyse is null then 0 else a.bqyse end,case when yishoue is null then 0 else yishoue end,
(table1.shangqiyue-case when a.bqyse is null then 0 else a.bqyse end+case when yishoue is null then 0 else yishoue end) qianfee from table1,a,b
where table1.bianhao = a.bianhao and table1.bianhao = b.bianhao and
(table1.shangqiyue-case when a.bqyse is null then 0 else a.bqyse end+case when yishoue is null then 0 else yishoue end)<0
把outer 去掉试试。
table2.bianhao=table1.bianhao left outer join table3 on table1.bianhao=table3.bianhao
要走了,实在不行,你用第二种方法看看。