在排序依据后选择一条记录

Question

我有一堆CD里面有一堆艺术品。我想在网页上显示每张CD的缩略图，为此，我需要从该CD的许多现有艺术作品中选择一个图像...

该查询将选择所有图稿，I group by position，并希望选取第一个图稿。我不知道如何在排序后只为每个记录挑选第一张图像…

查询如下：

SELECT release_artwork.release_id, release_artwork.position, artwork.small_filename, artwork.small_width, artwork.small_height
FROM release_artwork
JOIN artwork ON artwork.artwork_id = release_artwork.artwork_id
ORDER BY release_artwork.position

​

​

Answer 1

我手边没有MySQL实例，但这应该可以工作-只需定义一个内联表来保存最小值，然后连接到该表。

SELECT release_artwork.release_id, release_artwork.position, artwork.small_filename, artwork.small_width, artwork.small_height
FROM
  release_artwork
  inner JOIN artwork ON artwork.artwork_id = release_artwork.artwork_id
  inner join
  (
  SELECT
    ra.release_id as release_id
   ,min(ra.position) as rap
  FROM
    release_artwork ra
  group by
    ra.release_id
  ) mins
  on mins.release_id = release_artwork.release_id
    and mins.rap = release_artwork.position
ORDER BY
  release_artwork.release_id

Answer 2

在这种情况下，您可以使用LIMIT 1将检索的结果限制为只检索第一个结果。

Answer 3

SELECT release_artwork.release_id, release_artwork.position, artwork.small_filename, artwork.small_width, artwork.small_height
FROM release_artwork
JOIN artwork ON artwork.artwork_id = release_artwork.artwork_id
ORDER BY release_artwork.position LIMIT 1

在末尾添加LIMIT 1会将其限制为单个结果。