另外100分在
http://expert.csdn.net/Expert/topic/1628/1628622.xml?temp=.5162317这里有三张表,三张之间以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控件里把当前没有停机的所有的欠费的bianhao都出来。。应该怎么做?我举个实例吧:例如
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 700
这时候我需要查出有哪些人欠费:13611111111的当前余额=3900+200-4200=-100 表示这个号欠费。
13622222222的当前余额=700+300-1200=-200 表示这个号也欠费。 这个时候我需要在DBGrid显示出来的信息应该是:bianhao shangqiyue(上期余额) yingshou(本期应收) yishou(本期已收) jiyu(当前余额)13611111111 200 4200 3900 -100
13622222222 300 1200 700 -200 我应该怎么做?
http://expert.csdn.net/Expert/topic/1628/1628622.xml?temp=.5162317这里有三张表,三张之间以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控件里把当前没有停机的所有的欠费的bianhao都出来。。应该怎么做?我举个实例吧:例如
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 700
这时候我需要查出有哪些人欠费:13611111111的当前余额=3900+200-4200=-100 表示这个号欠费。
13622222222的当前余额=700+300-1200=-200 表示这个号也欠费。 这个时候我需要在DBGrid显示出来的信息应该是:bianhao shangqiyue(上期余额) yingshou(本期应收) yishou(本期已收) jiyu(当前余额)13611111111 200 4200 3900 -100
13622222222 300 1200 700 -200 我应该怎么做?
解决方案 »
- VCL组件 + 设计模式 = ?
- 如何写一个参数是多维数组的function和调用方法
- 有没有人比较熟悉仓储系统?该怎么设计
- 大家说说初学delphi该看哪些书?(参与者有分!)
- 驱动被360拦截 内详
- 代码写的TAdoQuery怎么连接不到控件TadoConnection呢,请高手帮忙看下
- 求LIS\PACS源代码
- 如何让程序响应一个按键序列?---初学者
- 请问各位高手,web网页中alert('程序错误!')是象windows发什么消息?(高分:100)
- 如何存取Oracle Number(18)的字段?
- 哪里有COM/COM+/DCOM/MTS的入门书籍下载或入门文章的网站?
- 请问用几个AdoTable同时插入数据,怎样实现Sql的事务控制?
group by bianhao
group by bianhao
from table3
group by bianhao
2、select bianhao, sum(feiyong1) feiyong1,sum(feiyong2) feiyong2 ,sum(otherfei) otherfei
from table2
group by bianhao
(select bianhao,sum(feiyong1) as f1,sum(feiyong2) as f2, sum(otherfei) as f3 from table2 group by bianhao ) as a,
(select bianhao, sum(shoufeijine) as sf from table3 group by bianhao ) as b
where
table1.shangqiyue+b.sf-a.f1-a.f2-a.f3<0 and
table1.bianhao=a.bianhao and b.bianhao=a.bianhao
select a.bianhao, shangqiyue,sum(feiyong1+feiyong2+otherfei) yingshou,
sum(shoufeijine) yishou ,(yishou-yingshou) jiyu
from table1 a,table2 b,table3 c
group by a.bianhao,shangqiyue
dbgrid.datasource.dataset.sql.text:=
select bianhao, sum(feiyong1)+sum(feiyong2)+sum(otherfei) as yingshoufei into #temp1 group by bianhao select bianhao, sum(shoufeijine) as jiaofei into #temp2 group by bianhao select a.bianhao,b.yingshoufei ,c.jiaofei ,a.shangqiyue+c.jiaofei-b.yingshoufei as danqianyue where (b.bianhao=a.bianhao) and (c.bianhao=a.bianhao)
drop #temp1
drop #temp2
dbgrid.datasource.dataset.sql.Open;
(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 table2 left outer join table1 on table2.bianhao=bable1.banhao left outer join table3 on table1.bianhao=table3.bianhao group by table2.bianhao where jieyu<0
sum(shoufeijine) yishou ,(yishou-yingshou) jiyu
from table1 a,table2 b,table3 c
where jiyu<0
group by a.bianhao,shangqiyue
有误,改成:
select bianhao, sum(feiyong1)+sum(feiyong2)+sum(otherfei) as yingshoufei into #temp1 from table2 group by bianhao select bianhao, sum(shoufeijine) as jiaofei into #temp2 from table3 group by bianhao select a.bianhao,b.yingshoufei ,c.jiaofei ,a.shangqiyue+c.jiaofei-b.yingshoufei as danqianyue from table1 a, #temp1 b #temp2 c where (b.bianhao=a.bianhao) and (c.bianhao=a.bianhao)
(select sum(feiyong1+feiyong2+otherfeiyong) from table2 where bianhao=table1.bianhao),
yishou =
(select sum(shoufeijine) from table3 where bianhao=table1.bianhao),
jiyu =
shangqiyue + (select sum(shoufeijine) from table3 where bianhao=table1.bianhao) -
(select sum(feiyong1+feiyong2+otherfeiyong) from table2 where bianhao=table1.bianhao)
from table1 where tingjitime IS NULL and
shangqiyue + (select sum(shoufeijine) from table3 where bianhao=table1.bianhao) -
(select sum(feiyong1+feiyong2+otherfeiyong) from table2 where bianhao=table1.bianhao)<0
a.shangqiyue,
(select sum(b.feiyong1+b.feiyong2+b.otherfei) from b where b.bianhao=a.bianhao) as yingshou,
(select sum(c.shoufeijine) from c where b.bianhao=a.bianhao) as yishou, (yishou-yingshou) as jiyu
from table1 a, table2 b, table3 c where a.bianhao=b.bianhao and a.bianhao=c.bianhao order by a.bianhao
select a. bianhao,
a.shangqiyue,
(select sum(b.feiyong1+b.feiyong2+b.otherfei) from table2 where b.bianhao=a.bianhao) as yingshou,
(select sum(c.shoufeijine) from table3 where b.bianhao=a.bianhao) as yishou, (yishou-yingshou) as jiyu
from table1 a, table2 b, table3 c where a.bianhao=b.bianhao and a.bianhao=c.bianhao order by a.bianhao
你的table2为应付,可以不管(该付款认为0,则不欠费)
字段必须一一对应
select * from table1 a,table2 b
where a.bianhao=b.bianhao and
a.bianhao not in (select bianhao from table3)
union
select .......(我原来写的东西)
shoufeijine decimal(19.2),Yue decimal(19,2) )
insert into #Temp1 select bianhao,shangqiyue,0,0,0 from table1
update #Temp1 set feiyong=sum(feiyong1+feiyong2+otherfeiyong) from #Temp1 a where #Temp1.bianhao=a.bianhao
group by a.bianhao
update #Temp1 set shoufeijine=sum(shoufeijine) from #Temp1 a where #Temp1.bianhao=a.bianhao
group by a.bianhao
update #Temp1 set yue=shangqiyue+shoufeijine-feiyong
select * from #Temp1 a,table b where a.bianhao=b.bianhao and a.yue<0 and b.tingtime<>'' and b.tingtime>getdate()
drop table #Temp1
再试试
(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