mysql加入限制1 [英] mysql join with limit 1
问题描述
假设我有两个表一对多.
Suppose I have two tables in one to many relationship.
而且,我想从每个主记录中选择列,并从相关表中选择第一条记录.
And, I want to select columns from each main record along with the first record from a related table.
我尝试了一些方法,但是没有成功...
I tried some ways but it just doesn't go...
在这里,我得到了这个SQL提琴:
Here I end up with this SQL fiddle:
http://sqlfiddle.com/#!2/39fdb/3
那里的问题是它不能从子选择中引用a.ID.
The problem there is that it just cannot reference a.ID from a subselect.
这当然不起作用,但这只是我能想到的
This does not work, of course, but it's just all I could think of
select a.*,b.* from event a left join
(select * from event_pictures where a.ID=article limit 1)
b on a.ID=b.article;
关于如何解决它的任何想法?
Any ideas on how to fix it?
推荐答案
不,您不能在与a
相连的子选择中引用a.ID
.您可以执行以下操作,但是最好提供一个订购单.否则,将没有第一"行.将选择表b中的(或多或少)随机行:
No, you can't reference a.ID
in a subselect that is joined to a
. You can do the following, but you better supply an ordering. Otherwise, there is no "first" row. A (more or less) random row from table b will be selected:
select a.*, b.*
from event a
left join event_pictures b
on b.PK = --- the PRIMARY KEY
( select bb.PK --- of event_pictures
from event_pictures bb
where a.ID = bb.article
ORDER BY bb.something
limit 1
) ;
这篇关于mysql加入限制1的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!