MySQL 连接两个表中的 LIMIT 1
我有两张桌子。一份包含有关属性的信息。另一个为每个属性存储 3 个图像。在这三幅图像中,一幅被标记为“主”图像。
所以我有:
Properties:
p_id name
1 villa a
2 villa b
3 villa c
4 villa d
并且
Images
i_id p_id main
1 1 0
2 1 0
3 1 1
4 2 0
5 2 1
6 2 0
我需要生成一个查询,该查询返回所有属性及其主图像的 id。例如,
p_id name i_id
1 villa a 3
2 villa b 5
我知道这将涉及使用 LIMIT 1 和连接,但不知道从哪里开始,我已经尝试通过使用子查询来做到这一点,但觉得它一定比我正在做的复杂......
*我该怎么做* 使其通过“main”选择前 1 个来对查询进行排序(即,如果未设置 main,它仍会选择图像)?
I have two tables. One with information about properties. The other stores 3 images for each property. Of these three images - one is marked as being the "main" image.
So I have:
Properties:
p_id name
1 villa a
2 villa b
3 villa c
4 villa d
and
Images
i_id p_id main
1 1 0
2 1 0
3 1 1
4 2 0
5 2 1
6 2 0
I need to produce a query which returns all of the properties with the id of their main image. e.g.
p_id name i_id
1 villa a 3
2 villa b 5
I know this will involve using LIMIT 1 and a join, but not sure where to start, I have already attempted doing this by using a subquery but felt it must be less complicated than what I was doing....
* HOW DO I *
Make it so it orders the query by "main" selecting the top 1 (i.e. so if main is not set it will still select an image) ?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
这是一种方法:
如果未找到主图像,左连接将返回 NULL 图像。
Here's one way:
The left join will return a NULL image if no main image is found.
给你:
或者
Here you go:
or
您想要使用如下查询:
You want to use a query like the following: