无法弄清楚如何编写查询，非常感谢

Question

我一直在试图编写一个查询的最后几个小时，我无法弄清楚，我是新来的SQL和答案会在我疯狂之前，我会非常感激。

这里是我的表，

CREATE TABLE offences (
oOffenceID VARCHAR (30), 
oDescription VARCHAR (200), 
oMaximumFine INT (10), 
CONSTRAINT o_pk 
PRIMARY KEY (oOffenceID) 
); 

CREATE TABLE drivers (
dPersonID VARCHAR (30), 
dLicenseNumber INT (20), 
dLicenseRemoved ENUM('yes','no'), 
dExpiryDate DATE, 
CONSTRAINT d_pk 
PRIMARY KEY (dLicenseNumber), 
CONSTRAINT d_fk 
FOREIGN KEY (dPersonId) 
REFERENCES People (pPersonID) 
); 

CREATE TABLE people (
pPersonID VARCHAR (30), 
pName VARCHAR (30), 
pAddress VARCHAR (50), 
pNIN VARCHAR (30), 
CONSTRAINT p_pk 
PRIMARY KEY (pPersonID) 
); 

CREATE TABLE vehicle (
vOwnerID VARCHAR (30), 
vColour VARCHAR (30), 
vModel VARCHAR (30), 
vMake VARCHAR (30), 
vVehicleID VARCHAR (30), 
CONSTRAINT v_pk 
PRIMARY KEY (vVehicleID), 
CONSTRAINT v_fk 
FOREIGN KEY (vOwnerID) 
REFERENCES People (pPersonID) 
); 

CREATE TABLE fines (
fFineID INT (30) AUTO_INCREMENT, 
fVehicleID VARCHAR (30), 
fPersonID VARCHAR (30), 
fTime DATE, 
fAmount INT (10), 
fOfficerStatement VARCHAR (500), 
fOffenceID VARCHAR (30), 
CONSTRAINT f_pk 
PRIMARY KEY (fFineID), 
CONSTRAINT f_fk 
FOREIGN KEY (fVehicleID) 
REFERENCES vehicle (vVehicleID), 
CONSTRAINT f_fk1 
FOREIGN KEY (fPersonID) 
REFERENCES people (pPersonID), 
CONSTRAINT f_fk2 
FOREIGN KEY (fOffenceID) 
REFERENCES offences (oOffenceID) 
); 

我需要一个查询，显示汽车VehicleID，业主的姓名和累计已累计超过1000罚款的所有车的罚款总额。任何人都可以看到如何做到这一点？

Answer 1

希望这将让你在正确的方向前进：

select fVehicleId,p.pName,sum(fi.totalFines) as totalFinesOver1000ForPerson 
from 
(select fVehicleId,sum(fAmount) as totalFines 
from fines f 
group by fVehicleId 
having sum(fAmount) > 1000) fi 
inner join vehicle v on v.vVehicleID = fi.fVehicleId 
inner join people p on v.vOwnerId = p.pPersonId 
group by fVehicleId,p.pName; 

Answer 2

Select a.vVehicleID, 
     b.pName, 
     sum(d.fAmount) fine 
from vehicle a,people b,fines d,drivers e 
where a.vVehicleID=d.vVehicleID and d.fPersonID=e.dPersonID and d.fPersonID=a.vOwnerID and 
     b.pPersonID = e.dPersonID 
group by a.vVehicleID,b.pNamem 
having fine > 1000; 

这应该工作