使用具有相同外键的两列

Question

我有一个数据库，其中有 5 个表，但我有问题。在发货表中，有两列，来源和目的地，它们都是对路线表的外键引用，但是当我从发货表中选择一条记录时，它只会显示两者相同的路线名称。

这是代码：

SELECT fare, commission, driver,shipment._date, routename, vehiclenumber, productname, source, destination, routename,ownername
FROM route, shipment, product, vehicle,owner
WHERE vehicle.vehicleid = shipment.vehicle
AND shipment.source
and vehicle.owner=owner.ownerid
AND shipment.destination = route.routeid
AND shipment.product = product.productid
AND vehicle.vehiclenumber =  'nk-234'
ORDER BY _date
LIMIT 0 , 30

Answer 1

要将一个表中的一条记录（一次发货）连接到另一个表中的多个记录（两条路线），理想情况下，您最好对 route 表使用显式 JOIN 两次（或更多，无论您需要多少链接）。

这是对查询的快速修改，演示了用法。请特别注意 SELECT 中的两个 routename 列以及到 route 表的两个 JOIN FROM：

SELECT fare,
       commission,
       driver,
       shipment._date,
       RS.routename,    <-- field from first join
       vehiclenumber,
       productname,
       source,
       destination,
       RD.routename,    <-- field from second join
       ownername

FROM shipment
JOIN route RS ON RS.routeID = shipment.source       <-- join 1; source
JOIN route RD ON RD.routeID = shipment.destination, <-- join 2; destination
     product,
     vehicle,
     owner

WHERE vehicle.vehicleid = shipment.vehicle
  AND vehicle.owner = owner.ownerid
  AND shipment.product = product.productid
  AND vehicle.vehiclenumber =  'nk-234'

ORDER BY _date

LIMIT 0 , 30

显然，这可能不完全符合您的需求，因为我不知道其他选定字段来自哪些表，以确保它们都被考虑在内......

Answer 2

这是因为您从发货中的一行中选择字段，为了真正从外键中获得任何效果，您需要使用一些连接

http://dev.mysql.com/doc/refman/5.5/en/join.html

Answer 3

使用旧式（非首选）符号，您可以这样写：

SELECT s.fare, s.commission, s.driver, s._date, v.vehiclenumber, p.productname,
       s.source, s.destination, rs.routename, rd.routename, o.ownername
  FROM route AS rs, route AS rd, shipment AS s, product AS p, vehicle AS v, owner AS o
 WHERE s.vehicleid = s.vehicle
   AND s.source = rs.routeid
   AND v.owner = o.ownerid
   AND s.destination = rd.routeid
   AND s.product = p.productid
   AND v.vehiclenumber =  'nk-234'
 ORDER BY _date
 LIMIT 0, 30

我必须猜测哪个表包含票价、佣金和司机信息；我以为它们都在发货表中。

标准 SQL 中并不严格允许以下划线开头的列名，但大多数 DBMS 允许这样做。但它看起来很丑；为什么不使用“ship_date”作为列名？

最好使用显式连接表示法，尤其是因为忘记连接条件的可能性较小：

SELECT s.fare, s.commission, s.driver, s._date, v.vehiclenumber, p.productname,
       s.source, s.destination, rs.routename, rd.routename, o.ownername
  FROM shipment AS s
  JOIN route    AS rs ON s.source      = rs.routeid
  JOIN route    AS rd ON s.destination = rs.routeid
  JOIN vehicle  AS v  ON s.vehicleid   = s.vehicle
  JOIN owner    AS o  ON v.owner       = o.ownerid
  JOIN product  AS p  ON s.product     = p.productid
 WHERE v.vehiclenumber = 'nk-234'
 ORDER BY s._date
 LIMIT 0, 30