mysql - 如何在MySQL中通过查询从每个帖子的照片表中选择一张照片

Question

我有两个表，我想为每个帖子显示一张照片，但是当运行查询为每张照片显示帖子时

照片表:

+------------------------------------------------------------+
|  photo_id   |    post_id    |    photo_name                |
+------------------------------------------------------------+
|     1       |        1      |    flower.jpg                |
+------------------------------------------------------------+
|     2       |        1      |    book.jpg                  |
+------------------------------------------------------------+
|     3       |        2      |    pen.jpg                   |
+------------------------------------------------------------+
|     4       |        3      |    man.jpg                   |
+------------------------------------------------------------+
|     5       |        3      |    women.jpg                 |
+------------------------------------------------------------+
|     6       |        3      |    boy.jpg                   |
+------------------------------------------------------------+

张贴表:

+--------------------------------+
|  post_id    |   post_title     |
+--------------------------------+
|     1       |   flower         |
+--------------------------------+
|     2       |   book           |
+--------------------------------+
|     3       |   pen            |
+--------------------------------+
|     4       |   man            |
+--------------------------------+
|     5       |   women          |
+--------------------------------+
|     6       |   boy            |
+--------------------------------+

使用流动的 SQL 查询和 INNER JOIN 但我的结果显示如下:

SELECT
Photo.photo_name,
Post.post_title
FROM Photo
INNER JOIN Post
ON Photo.post_id = Post.post_id 
ORDER BY Post.post_id 

执行后查询结果:

+-----------------------------+
|  post_title |   photo_name  |
+-----------------------------+
|     flower  |  flower.jpg   |
+-----------------------------+
|     flower  |  flower.jpg   |
+-----------------------------+
|     book    |  book.jpg     |
+-----------------------------+
|     book    |  book.jpg     |
+-----------------------------+
|     pen     |  pen.jpg      |
+-----------------------------+
|     pen     |  pen.jpg      |
+-----------------------------+
|     man     |  man.jpg      |
+-----------------------------+
|     man     |  man.jpg      |
+-----------------------------+
|     women   |  women.jpg    |
+-----------------------------+
|     women   |  women.jpg    |
+-----------------------------+
|     boy     |  boy.jpg      |
+-----------------------------+
|     boy     |  boy.jpg      |
+-----------------------------+

我在帖子表中有 6 个帖子，我想为每个帖子获取一张照片，但我的结果按照片数显示帖子

Answer 1

您可以使用分组方式。由于您还没有定义，在帖子有多张照片的情况下使用哪张照片，我们可以获得帖子的 Max() 照片:

SELECT
MAX(Photo.photo_name),
Post.post_title
FROM Photo
INNER JOIN Post
ON Photo.post_id = Post.post_id 
GROUP BY Post.post_id, Post.post_title 
ORDER BY Post.post_id