如何在mysql中通过查询从photo表中为每个post选择一张照片

brjng4g3 于 2021-06-18 发布在 Mysql

关注(0)|答案(2)|浏览(392)

我有两个表，我想为每个帖子显示一张照片，但当运行查询show post for every photo时
照片表：

+------------------------------------------------------------+
|  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查询和内部联接，但我的结果如下所示：

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个职位的职位表，我想得到一张照片，为每个职位，但我的结果显示，按照片计数后

sql mysql sqlcommand

来源：https://stackoverflow.com/questions/52759348/how-to-select-a-photo-from-the-photo-table-for-each-post-by-query-in-mysql

2条答案

按热度按时间

sbdsn5lh1#

假设你希望所有的帖子至少有一张照片（我不知道是哪一张）。所以，我愿意 LEFT JOIN 带子查询：

SELECT pst.post_title, p.photo_name
FROM Post pst LEFT JOIN 
     Photo p
     ON p.post_id = pst.post_id AND 
        p.photo_id = (SELECT MAX(p1.photo_id)
                      FROM Photo p1
                      WHERE p1.post_id = p.post_id
                     );

赞(0）回复(0）举报 2021-06-18

mmvthczy2#

你可以用 group by . 由于您还没有定义，在一篇文章有多张照片的情况下使用哪张照片，我们可以得到 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

赞(0）回复(0）举报 2021-06-18

我来回答

如何在mysql中通过查询从photo表中为每个post选择一张照片

2条答案

相关问题

热门标签

最新问答