以下查询返回错误:
ORA-00904: "C"."CHARGEDATE": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error en la línea: 6, columna: 58
查询如下:
Select
c.idChargeStation,
c.idVehicleUnit,
round(cc.totalTiempo/cc.totalCargas, 2) AS media,
v.versionNameVU,
c.chargeDate,
p.firstName,
p.lastName
FROM
Charge c,
VehicleUnit v,
Person p,
(
SELECT
idChargeStation,
idVehicleUnit,
chargeDate,
SUM(totalPluggedTime) AS totalTiempo,
count(*) AS totalCargas
FROM Charge
WHERE
idChargeStation = c.idChargeStation
AND idVehicleUnit = c.idVehicleUnit
AND chargeDate = c.chargeDate
) cc
WHERE
c.idVehicleUnit = v.idVehicleUnit
AND v.idClient = p.idPerson
HAVING media > 600
HAVING round(c.powerKwH/c.KmFromLast, 2) > 1.00
Order by c.chargeDate DESC;
共有3个表: Charge , VehicleUnit , Person :
idVehicleUnit 是 VehicleUnit 的外键( idVehicleUnit = idVehicleUnit )
idClient 是 Person 的外键( idClient = idPerson )
我添加信息,组成与每个表的前10个记录相关的表 .
表费:
表VehicleUnit:
表人:
查询必须返回计费表的idChargeStation,idVehicleUnit,chargeDate字段,totalPluggedTime平均值,vehicleUnit表的versionNameVU字段以及Person表的firstName和lastName字段 .
平均值通过以下公式获得:SUM(totalPluggedTime)AS totalTime count(*)AS totalLoads