使用INNER JOIN查询时出现错误

Question

我还在学习MySQl。这是关系DBMS:

CUSTOMER (CustID, CustName, AnnualRevenue)
TRUCK (TruckNumber, DriverName)
CITY (CityName, Population)
SHIPMENT (ShipmentNumber, CustID, Weight, Year, TruckNumber, CityName)

现在，我必须为这两个查询制定:

1. 各城市每年货物总重量。
2. 将货物运往伦敦但不运往巴黎的司机。

这些是我提出的查询:1 .

select sum(s.weight), s.year , c.city
from shipment s, city c
INNER JOIN CITY
on s.CityName = c.CityName

Answer 1

您正在混合使用JOIN表的旧方法(您应该避免这种方法，因为连接列没有明确声明，这会使其他人感到困惑):

FROM shipment s, city c

应该对select中未聚合的列(年份、城市)进行分组。此外，最好为聚合列使用别名(如total_weight)

select sum(s.weight) AS total_weight, s.year , c.city
from shipment s
INNER JOIN CITY as c
on s.CityName = c.CityName
GROUP BY s.year, c.city

尝试解决第二个查询，如果有问题再回来。