mysql - 如何限制 SQL 子查询或连接的结果

Question

假设我在 MySQL 中有 2 个表，一个名为 Vehicle，另一个名为 Passenger。

如果我想要所有车辆及其乘客的完整列表，那么我可以这样做:

SELECT * 
  FROM Vehicle v 
  LEFT 
  JOIN Passenger p
    ON p.VehicleID = v.VehicleID 
 LIMIT 0,100

这里的问题是假设我的车辆是公共(public)汽车，第一辆有 50 名乘客，第二辆有 40 名，第三辆有 30 名。上述查询中的 Limit 100 会给我部分乘客列表第 3 路公交车。

有没有一种方法可以创建这样一个不会从连接表中拆分结果的查询？

或者您可以将 LIMITS 分别应用于不同的表吗？所以我可以说我想要限制 10 辆车，每辆车限制 50 名乘客？

逻辑上是这样的:

SELECT * FROM Vehicle (LEFT JOIN Passenger ON Passenger.VehicleID = Vehicle.VehicleID LIMIT 0,50) LIMIT 0, 10

我想知道这是否可以使用某种子查询来实现？也许是这样的:

SELECT *, (SELECT * FROM Passenger WHERE Passenger.VehicleID = Vehicle.VehicleID LIMIT 0,50) FROM Vehicle LIMIT 0, 10

但这不起作用(子查询只允许返回单行)。

提前致谢。

Answer 1

在 MySQL 中，最简单的方法是使用变量来枚举行:

SELECT * 
FROM (SELECT v.*, (@rnv := @rnv + 1) as seqnum_v
      FROM Vehicle v CROSS JOIN
           (SELECT @rnv := 0) params
     ) v LEFT JOIN
     (SELECT p.*,
             (@rnp := if(@v = VehicleId, @rnp + 1,
                         if(@v := VehicleId, 1, 1)
                        )
             ) as seqnum_p
      FROM Passenger p CROSS JOIN
           (SELECT @v := -1, @rnp := 0) params
     ) p
     ON p.VehicleID = v.VehicleID 
WHERE seqnum_v <= 10 and seqnum_p <= 50;