表web_stat_vehicle
表vehicle_def
C:\Documents and Settings\zcj\桌面\新建 BMP 图像 (2).bmp
代码如下:
select
license,sum(patrol_circle),sum(miles),vehicle_state,over_time
from
web_stat_vehicle a,vehicle_def b
where
a.vehicle_id=b.vehicle_id and
org_id =9 and
date(start_time)=date('2009-04-03')
group by a.vehicle_id,vehicle_state而我想要的结果是:
车牌号 巡逻圈数 巡逻里程 巡逻状态(0为巡逻,1为停留) 巡逻/停留时间(随巡逻状态改变)
license patrol_circle miles vehicle_state over_time
苏A12345 1000.000 122.124 1 10.000
苏A12346 2653.000 37.000 0 14.000
苏A12346 2653.000 37.000 1 20.000
请大家仔细看,也就是说相同的license再根据vehicle_state分组获得结果,共用patrol_circle,miles.
请问此语句该怎么写 ?
表vehicle_def
C:\Documents and Settings\zcj\桌面\新建 BMP 图像 (2).bmp
代码如下:
select
license,sum(patrol_circle),sum(miles),vehicle_state,over_time
from
web_stat_vehicle a,vehicle_def b
where
a.vehicle_id=b.vehicle_id and
org_id =9 and
date(start_time)=date('2009-04-03')
group by a.vehicle_id,vehicle_state而我想要的结果是:
车牌号 巡逻圈数 巡逻里程 巡逻状态(0为巡逻,1为停留) 巡逻/停留时间(随巡逻状态改变)
license patrol_circle miles vehicle_state over_time
苏A12345 1000.000 122.124 1 10.000
苏A12346 2653.000 37.000 0 14.000
苏A12346 2653.000 37.000 1 20.000
请大家仔细看,也就是说相同的license再根据vehicle_state分组获得结果,共用patrol_circle,miles.
请问此语句该怎么写 ?
表web_stat_vehicle:
子增列 机构编号 车编号 车状态 巡逻/停留时间 巡逻圈数 巡逻里程 开始时间
sv_id org_id vehicle_id vehicle_state over_time patrol_circle miles start_time
1 9 1 1 10.000 1000 122.124 2009-4-3 10:20:20
2 9 2 0 14.000 1254 12.000 2009-4-3 1:00:00
3 9 2 0 24.000 1045 20.000 2009-4-3 5:00:00
4 10 2 1 20.000 354 5.000 2009-4-3 6:30:20
5 10 6 0 15.000 744 10.000 2009-4-5 6:00:05
6 10 8 0 21.000 250 23.000 2009-3-12 10:20:20
表vehicle_def:
车编号 车号
1 苏A12345
2 苏A12346
sv_id org_id vehicle_id vehicle_state over_time patrol_circle miles start_time
2 9 2 0 14.000 1254 12.000 2009-4-3 1:00:00
3 9 2 0 24.000 1045 20.000 2009-4-3 5:00:00
4 10 2 1 20.000 354 5.000 2009-4-3 6:30:20
license patrol_circle miles vehicle_state over_time苏A12346 2653.000 37.000 0 14.000
苏A12346 2653.000 37.000 1 20.000 select license,b.s_patrol_circle,s_miles,vehicle_state,sum(over_time)
from web_stat_vehicle a ,
vehicle_def b,
(select vehicle_id,sum(patrol_circle) as s_patrol_circle,sum(miles) as s_miles
from web_stat_vehicle
group by vehicle_id) c
where a.vehicle_id=b.vehicle_id
and a.vehicle_id=c.vehicle_id
and date(start_time)=date('2009-04-03')
group by license,b.s_patrol_circle,s_miles,vehicle_state好象和 org_id =9 没什么关系,所以去掉了。
select(第一行)后、group by后(最后一行)中的b.s_patrol_circle应该是c.s_patrol_circle多谢“acmain”,送分45(解决问题分)
多谢“aaaa”,送分5(热情分)多谢。
license patrol_circle miles vehicle_state over_time
苏A12345 1000.000 122.124 1 10.000
苏A12346 2653.000 37.000 0 38.000 (原来为14,我写错了,惭愧)
苏A12346 2653.000 37.000 1 20.000
本人java还行,弱项是sql语句,很是困惑,不知有何方法能提高此能力?
望两位专家指导后辈...
感谢...