这里有三张表,三张之间以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都出来。。应该怎么做?
name(姓名)
shangqiyue(上期余额)
tingjitime(停机时间)table2---应收费用
bianhao(编号)
yuefen(月份)
feiyong1(费用1)
feiyong2(费用2)
otherfeiyong(其他费用)其中各月的:feiyong1(费用1)+feiyong2(费用2)+otherfeiyong(其他费用)=本期已收金额
table3----已收费用 (本期发生的所有的费用全部放在一个表中)bianhao(编号)
shoufeitime(收费时间)
shoufeijine(收费金额)其中:所有的收费金额之和为本期应收金额:
最后这三张表连接起来又形成了一个公式:shangqiyue(上期余额)+本期已收金额-本期应收金额=当前余额(若当前余额<0就表示欠费)
这时候,我想在一个窗口的DBgrid控件里把当前没有停机的所有的欠费的bianhao都出来。。应该怎么做?
解决方案 »
- Delphi7 中如何添加.exe的控件?
- delphi7的treeview的treenode有tag属性吗,没有如何加tag属性?
- delphi5源代码转换成delphi6代码
- 我想在EXE文件中引用DLL中的一个Frame,但出现错误!
- 刚下了个RichViewEdit,但不会用,尤其是怎么设上下标
- 急!DBGRID如何随列的实际宽度动态变化,在线等待!
- 那位老大提供一个delphi7连接sql server 2000数据库的范例程序啊?重分酬谢200分!分两次给。
- 怎样写一个没有窗口的程序(不是console)?
- 如何编写GIS系统
- CRYSTAL REPORTS报表数据问题,看看你有没有更好的?
- 我用sqlserver做数据库客户端不装sqlserver客户端但用bde连接时db-library网络通信层
- 找不到所需的 .DLL 文件 - VCLADO50.BPL
from table1 a,table2 b,table3 c
where a.bianhao=b.biaohao and
a.bianhao=c.bianhao and
b.bianhao=c.bianhao and
(a.tingjitime<>"" or a.tingjitime is null)
group by a.bianhao
having sum(a.shangqiyue+b.feiyong1+b.feiyong2+b.otherfeiyong-c.shoufeijine)<0
你试试看。
from table1 a,table2 b,table3 c
where a.bianhao=b.biaohao and
a.bianhao=c.bianhao and
b.bianhao=c.bianhao and
(a.tingjitime<>"" or a.tingjitime is null)
group by a.bianhao
having sum(a.shangqiyue+b.feiyong1+b.feiyong2+b.otherfeiyong-c.shoufeijine)<0
query1-datasource1-dbgrid;
(a.tingjitime is null or a.tingjitime < '当前时间')
应该差不多了!
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 我应该怎么做?
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 我应该怎么做?
(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 bianhao ,Sum(Convert(int,shangqiyue)) as shangqiyue , Sum(0) as beny ,Sum(0) as benyj from tables1 group by bianhao
union all
select bianhao ,Sum(0) as shangqiyue , (Sum(convert(int,feiyong1)) + Sum(convert(int,feiyong2)) + Sum(convert(int,otherfeiyong ))) as beny ,Sum(0) as benyj from tables2 group by bianhao
union all
select bianhao ,Sum(0) as shangqiyue , Sum(0) as beny ,Sum(convert(int,shoufeijine)) as benyj from tables3 group by bianhao
) t group by bianhao第5个字段作一个计算字段
sum(shoufeijine) yishou ,(yishou-yingshou) jiyu
from table1 a,table2 b,table3 c
group by a.bianhao,shangqiyue
select a.bianhao, shangqiyue,sum(feiyong1+feiyong2+otherfei) yingshou,
sum(shoufeijine) yishou ,(yishou-yingshou) jiyu
from table1 a,table2 b,table3 c
where jiyu<0
group by a.bianhao,shangqiyue
,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
select a. bianhao, 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 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
......
where a.bianhao *= b.bianhao and b.bianhao *= c.bianhao
或 where a.bianhao left outs join b.bianhao and b.bianhao left outs join c.bianhao
这样写:
select distinct 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
,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
select distinct 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 order by a.bianhao
,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
ADOQuery_SaleKaSum.SQL.Add(str1+str2+str3);
ADOQuery_SaleKaSum.ExecSQL;
@Rkdh varchar(50),@Dwbm char(20),@Czy int
AS
Create Table #Temp1 (bianhao int,shangqiyue decimal(19,2),feiyong decimal(19,2),
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()
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取得的结果集等同于: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
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