我在数据库记录查询时使用了下列语句
select sum(车数*车速)/sum(车数) 平均速度 from 记录表
where ...当车数为0时出错!
请问该语句应该如何写呀?我用的是INTRBASE 4
我这样写也不对
select sum(车数*车速)/(case sum(车数)when 0 then 1 else sum(车数) end) 平均速度 from 记录表
where ...
select sum(车数*车速)/sum(车数) 平均速度 from 记录表
where ...当车数为0时出错!
请问该语句应该如何写呀?我用的是INTRBASE 4
我这样写也不对
select sum(车数*车速)/(case sum(车数)when 0 then 1 else sum(车数) end) 平均速度 from 记录表
where ...
解决方案 »
- 用TRegExpr 提取http://forum.csdn.net/ 中的csdn,表达式该怎样写?
- 关于DLL回调主程序窗体的问题,急~~,请帮帮忙
- 求一SQL语句
- DELPHI如何调用GetWindowThreadProcessId?
- 我是新手 在Delphi中用那些控件能做的象Windows优化大师那样的界面??
- oracle开发问题?
- 请教一个关于日期转换到字符串的问题。
- DBGrid 的SelectIndexChange事件
- installshied里如何获得一个路径的父目录?
- 求救:各位大虾,我想把任务栏设为自动隐藏,应该在程序里怎么办呀
- circluar unit reference to 'unit1'
- 我是新手 在Delphi中用那些控件能做的象Windows优化大师那样的界面??
having sum(车数)<>0
select H,F,15,'A',0,
SUM(XH) SXH, SUM(XH)*(select sum(XH) from yiV200404 where d=t.d and h=t.h and f=t.f)/SUM(XH) VXH,
FROM yiT200404 t
WHERE D =1 and (H>0 or (H=0 and F>=0)) and (H<23 or (H=23 and F<59))
GROUP BY H,F
select H,F,15,'A',0,
SUM(XH) SXH, SUM(XH)*(select sum(XH) from yiV200404 where d=t.d and h=t.h and f=t.f)/SUM(XH) VXH,
SUM(ZH) SZH, SUM(ZH)*(select sum(ZH) from yiV200404 where d=t.d and h=t.h and f=t.f)/SUM(ZH) VZH
FROM yiT200404 t
WHERE D =1 and (H>0 or (H=0 and F>=0)) and (H<23 or (H=23 and F<59))
GROUP BY H,F
不知和
(select sum(XH) from yiV200404 where d=t.d and h=t.h and f=t.f)
有什么区别?
yiT200404为流量
yiV200404为速度
都有几种车型XH ZH DH
我要将每条的流量*速度/总的流量得出平均速度谢谢
含义 天 时 分 大货 中货 小货数据的D H F 一一对应
SUM(XH) SXH,
SUM(XH)*(select sum(XH) from yiV200404
where d=t.d and h=t.h and f=t.f
having sum(xh)<>0 <==增加一句
)/SUM(XH) VXH,
FROM yiT200404 t
WHERE D =1 and (H>0 or (H=0 and F>=0)) and (H<23 or (H=23 and F<59))
having sum(xh)<>0 <==增加一句
GROUP BY H,F
select H,F,15,'A',0,
SUM(XH) SXH,
SUM(XH)*(select sum(XH) from yiV200404 where d=t.d and h=t.h and f=t.f having sum(xh)<>0 )/SUM(XH) VXH
FROM yiT200404 t
WHERE D =1 and (H>0 or (H=0 and F>=0)) and (H<23 or (H=23 and F<59))
GROUP BY H,F
having sum(xh)<>0 但写多个
select H,F,15,'A',0,
SUM(XH) SXH,
SUM(XH)*(select sum(XH) from yiV200404 where d=t.d and h=t.h and f=t.f having sum(xh)<>0 )/SUM(XH) VXH,
SUM(ZH)*(select sum(ZH) from yiV200404 where d=t.d and h=t.h and f=t.f having sum(zh)<>0 )/SUM(ZH) VZH
FROM yiT200404 t
WHERE D =1 and (H>0 or (H=0 and F>=0)) and (H<23 or (H=23 and F<59))
GROUP BY H,F
having sum(xh)<>0,sum(zh)<>0
就不对了,说having语句有问题
select H,F,15,'A',0,
SUM(XH) SXH,
VXH=
CASE
WHEN SUM(XH)=0 THEN 0
ELSE SUM(XH)*(select sum(XH) from yiV200404
where d=t.d and h=t.h and f=t.f)/SUM(XH) ,
SUM(ZH) SZH,
VZH=
CASE
WHEN SUM(ZH)=0 THEN 0
ELSE SUM(ZH)*(select sum(ZH) from yiV200404
where d=t.d and h=t.h and f=t.f)/SUM(ZH) ,
SUM(DH) SDH,
VDH=
CASE
WHEN SUM(DH)=0 THEN 0
ELSE SUM(DH)*(select sum(DH) from yiV200404
where d=t.d and h=t.h and f=t.f)/SUM(DH)
FROM yiT200404 t
WHERE D =1 and (H>0 or (H=0 and F>=0)) and (H<23 or (H=23 and F<59))
GROUP BY H,F
在SQL server 2000中可以
但在INTRBASE 4中CASE语句总不对我现在用StringGrid来显示,算出总和,然后用循环算一遍,为0直接置0
Intrabase中也可以实现。不过用其它语句。