谢谢paulowang(保罗),看样子这个分我只能给你了! 我总共要进行三次统计! 1)qqsql:= 'select * from maintb where datex Between #'; qqsql:= qqsql+yq1+'# and #'+yq2+'#'; qqsql:= qqsql +' and dlc=2 and rpc=5'; 2)qqsql:='select rp,datey,sum(f13+f14),sum(f15+f16),sum(f19),sum(f6),sum(f12) from grfx'; qqsql:=qqsql+' group by datey,rp having sum(f13+f14)<>0 '; qqsql:=qqsql+' and sum(f19)/3600>0.3'; 其中的grfx即为第一个sql语句的结果集,我没办法合并这两个语句,只好存为一个表, rp,datey,f13,f14,f15,f16,f19等都为字段名,在这里还要进行合并统计 3) qqsql:='select datey,count(*) from zhtb group by datey Order by datey'; 这里的zhbt为从第二个sql语句的结果集,同样我把他存为了一个表 **************************************************************** 总之,我的目的是想让这三个语句都在一个ADO记录集中操作,不要再多建两个表,请帮我看看吧,非常感谢!!!
请帮我写出这个SQL语句,谢谢!
qqsql := 'select datey, count(*) from (select rp, datey, sum(f13+f14) as temp1, sum(f15+f16) as temp2, sum(f19) as temp3, sum(f6) as temp4, sum(f12) as temp5 from (select * from maintb where datex Between #'+yq1+'# and #'+yq2+'#'+' and dlc=2 and rpc=5) as temptb1(maintb的所有字段) group by rp having temp1<>0 and temp3/3600 > 0.3) as temptb2(rp, datey, temp1, temp2, temp3, temp4, temp5) group by datey Order by datey'试试看,不敢保证能成功
select datey, count(*) as Mycount from (select rp, datey, sum(f13+f14) as temp1, sum(f15+f16) as temp2, sum(f19) as temp3, sum(f6) as temp4, sum(f12) as temp5 from (select * from maintb where datex Between 待定1 and 待定2 and dlc=2 and rpc1=5) as temptb1(rp,datex, datey, f6, f12, f13, f14, f15, f16, f19, dlc, rpc1) group by datey,rp having sum(f13+f14)<>0 and sum(f19)/3600 > 0.3) as temptb2(rp, datey, temp1, temp2, temp3, temp4, temp5) group by datey Order by datey上面的帖子有错,现修正 已在Query Analysis通过,但在Delphi中用Query不行,我Delphi不行的,搞不定 建议用存储过程,传两个参数进去
create procedure ProcedureName @yq1 varchar(50), @yq2 varchar(50) as select datey, count(*) as Mycount from (select rp, datey, sum(f13+f14) as temp1, sum(f15+f16) as temp2, sum(f19) as temp3, sum(f6) as temp4, sum(f12) as temp5 from (select * from maintb where datex Between @yq1 and @yq2 and dlc=2 and rpc1=5) as temptb1(rp,datex, datey, f6, f12, f13, f14, f15, f16, f19, dlc, rpc1) group by datey,rp having sum(f13+f14)<>0 and sum(f19)/3600 > 0.3) as temptb2(rp, datey, temp1, temp2, temp3, temp4, temp5) group by datey Order by datey
如建一个视图(名为 zhtb2),就将其作为一个“新表”,然后根据条件执行查询。
(select …… ) as result( …… ) 这里两个省略号代表相同的字段
group by datey
Order by datey'
result不是关键字,是一个临时的表名
sql规定,如果在from子句里使用查询,要给这个查询起个表名你试了没有?成功了吗?不成功就把完整的sql语句贴上来
我总共要进行三次统计!
1)qqsql:= 'select * from maintb where datex Between #';
qqsql:= qqsql+yq1+'# and #'+yq2+'#';
qqsql:= qqsql +' and dlc=2 and rpc=5';
2)qqsql:='select rp,datey,sum(f13+f14),sum(f15+f16),sum(f19),sum(f6),sum(f12) from grfx';
qqsql:=qqsql+' group by datey,rp having sum(f13+f14)<>0 ';
qqsql:=qqsql+' and sum(f19)/3600>0.3';
其中的grfx即为第一个sql语句的结果集,我没办法合并这两个语句,只好存为一个表,
rp,datey,f13,f14,f15,f16,f19等都为字段名,在这里还要进行合并统计
3) qqsql:='select datey,count(*) from zhtb group by datey Order by datey';
这里的zhbt为从第二个sql语句的结果集,同样我把他存为了一个表
****************************************************************
总之,我的目的是想让这三个语句都在一个ADO记录集中操作,不要再多建两个表,请帮我看看吧,非常感谢!!!
(select rp, datey, sum(f13+f14) as temp1, sum(f15+f16) as temp2, sum(f19) as temp3, sum(f6) as temp4, sum(f12) as temp5 from
(select * from maintb where datex Between #'+yq1+'# and #'+yq2+'#'+' and dlc=2 and rpc=5) as temptb1(maintb的所有字段)
group by rp having temp1<>0 and temp3/3600 > 0.3) as temptb2(rp, datey, temp1, temp2, temp3, temp4, temp5)
group by datey Order by datey'试试看,不敢保证能成功
(select rp, datey, sum(f13+f14) as temp1, sum(f15+f16) as temp2, sum(f19) as temp3, sum(f6) as temp4, sum(f12) as temp5 from
(select * from maintb where datex Between 待定1 and 待定2 and dlc=2 and rpc1=5)
as temptb1(rp,datex, datey, f6, f12, f13, f14, f15, f16, f19, dlc, rpc1)
group by datey,rp having sum(f13+f14)<>0 and sum(f19)/3600 > 0.3)
as temptb2(rp, datey, temp1, temp2, temp3, temp4, temp5)
group by datey Order by datey上面的帖子有错,现修正
已在Query Analysis通过,但在Delphi中用Query不行,我Delphi不行的,搞不定
建议用存储过程,传两个参数进去
你说的传两个参数到存储过程中去,怎么做,我对存储过程不太熟悉,用他很好吗?谢谢
分已经全给你了,希望以后能再得到你的帮助!
@yq1 varchar(50), @yq2 varchar(50)
as
select datey, count(*) as Mycount from
(select rp, datey, sum(f13+f14) as temp1, sum(f15+f16) as temp2, sum(f19) as temp3, sum(f6) as temp4, sum(f12) as temp5 from
(select * from maintb where datex Between @yq1 and @yq2 and dlc=2 and rpc1=5)
as temptb1(rp,datex, datey, f6, f12, f13, f14, f15, f16, f19, dlc, rpc1)
group by datey,rp having sum(f13+f14)<>0 and sum(f19)/3600 > 0.3)
as temptb2(rp, datey, temp1, temp2, temp3, temp4, temp5)
group by datey Order by datey