sql - 仅从 MySQL 连接返回唯一的表 ID

Question

我有一个MySQL有两个表的数据库(针对这个问题进行了简化):
电影表列:id、title、duration、description、created_at、updated_at
Rentals 表列:id、movie_id、status、created_at、updated_at

租借状态为“已 checkout ”或“已退回”，因此，如果电影没有关联的租借，或者所有相关的租借状态均为“已退回”，则该电影可用。如何查询可用的电影？现在我得到了这个:

SELECT rentals.id,rentals.status,movies.* 
FROM `movies` 
LEFT OUTER JOIN rentals ON movies.id = rentals.movie_id 
        AND movies.kiosk_id = rentals.kiosk_id 
WHERE (`movies`.kiosk_id = 1 
        AND (rentals.id is null or rentals.status != 'CHECKED OUT'));

问题是，如果电影已被 check out 多次，则此查询会返回电影的多个记录，因此我的 ORM 给了我重复的电影对象。有没有什么方法可以指定我只想要唯一的movie.id？我应该有一些不同的数据库架构吗？或者我应该以编程方式对其进行排序(似乎太慢了)？

Answer 1

我不确定你想要的租赁 ID 是什么，所以我把它拿出来了。

SELECT
  rentals.status, movies.*
FROM movies
LEFT OUTER JOIN (
  SELECT
    movie_id,
    kiosk_id,
    -- Because 'CHECKED OUT' comes alphabetically before 'RETURNED',
    -- this will have 'CHECKED OUT' if there's at least one rental checked out
    MIN(status) AS status
  FROM rentals
  GROUP BY movie_id, kiosk_id) rentals
ON movies.id = rentals.movie_id
AND movies.kiosk_id = rentals.kiosk_id
WHERE movies.kiosk_id = 1
AND (rentals.id is null or rentals.status != 'CHECKED OUT');