mysql - 获取相对于其他外键 id 最大的行

Question

我有两个表:Car 和 Rent

+----------+-----------+---------+--------+
| pk_carid | fk_rentid | name    | status |
+----------+-----------+---------+--------+
| 1        | 1         | toyota  | gone   |
+----------+-----------+---------+--------+
| 2        | 2         | tata    | here   |
+----------+-----------+---------+--------+
| 3        | 3         | ferrri  | here   |
+----------+-----------+---------+--------+
| 4        | 1         | toyota  | here   |
+----------+-----------+---------+--------+
| 5        | 2         | tata    | gone   |
+----------+-----------+---------+--------+
| 6        | 3         | ferrrii | gone   |
+----------+-----------+---------+--------+

<小时/>

我只想获取 pk_cardid 为 4、5 或 6 但不是 1、2 或 3 的记录。

我尝试了以下查询，但它只给了我最后一行的记录，其中pk_carid = 6。

<小时/>

SELECT c.cardid
       ,r.rentid
       ,c.name, 
FROM cars AS c 
     INNER JOIN rent AS r 
     ON r.rentid = c.carid
WHERE (c.carid =(SELECT MAX(c2.carid) Expr1 FROM cars c2 )) group by r.rentid

Answer 1

这应该有效:

select cars.* from cars
join
(
   select max(carid) as id  
   from cars 
   group by rentid
)as maxCar on maxCar.id = cars.carid

或者你可以尝试

select * from cars
where carid in
(
   select max(carid) as id  
   from cars 
   group by rentid
)