MySQL LEFT JOIN 仅第一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15626493/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LEFT JOIN only first row
提问by KddC
I read many threads about getting only the first row of a left join, but, for some reason, this does not work for me.
我阅读了很多关于只获取左连接第一行的线程，但是由于某种原因，这对我不起作用。
Here is my structure (simplified of course)
这是我的结构（当然是简化的）
Feeds
饲料
id | title | content
----------------------
1 | Feed 1 | ...
Artists
艺术家
artist_id | artist_name
-----------------------
1 | Artist 1
2 | Artist 2
feeds_artists
feeds_artists
rel_id | artist_id | feed_id
----------------------------
1 | 1 | 1
2 | 2 | 1
...
Now i want to get the articles and join only the first Artist and I thought of something like this:
现在我想获取文章并只加入第一个艺术家，我想到了这样的事情：
SELECT *
FROM feeds
LEFT JOIN feeds_artists ON wp_feeds.id = (
SELECT feeds_artists.feed_id FROM feeds_artists
WHERE feeds_artists.feed_id = feeds.id
LIMIT 1
)
WHERE feeds.id = '13815'
just to get only the first row of the feeds_artists, but already this does not work.
只是为了只获取 feeds_artists 的第一行，但这已经不起作用了。
I can not use TOPbecause of my database and I can't group the results by feeds_artists.artist_idas i need to sort them by date (I got results by grouping them this way, but the results where not the newest)
TOP由于我的数据库，我无法使用，我无法对结果进行分组，feeds_artists.artist_id因为我需要按日期对它们进行排序（我通过这种方式对它们进行分组得到了结果，但结果不是最新的）
Tried something with OUTER APPLY as well - no success as well. To be honest i can not really imagine whats going on in those rows - probably the biggest reason why i cant get this to work.
也用 OUTER APPLY 尝试了一些东西 - 也没有成功。老实说，我真的无法想象那些行中发生了什么 - 可能是我无法让它发挥作用的最大原因。
SOLUTION:
解决方案：
SELECT *
FROM feeds f
LEFT JOIN artists a ON a.artist_id = (
SELECT artist_id
FROM feeds_artists fa
WHERE fa.feed_id = f.id
LIMIT 1
)
WHERE f.id = '13815'
采纳答案by KddC
@Matt Dodges answer put me on the right track. Thanks again for all the answers, which helped a lot of guys in the mean time. Got it working like this:
@Matt Dodges 的回答让我走上了正轨。再次感谢所有的答案，同时帮助了很多人。让它像这样工作：
SELECT *
FROM feeds f
LEFT JOIN artists a ON a.artist_id = (
SELECT artist_id
FROM feeds_artists fa
WHERE fa.feed_id = f.id
LIMIT 1
)
WHERE f.id = '13815'
回答by Matt Dodge
If you can assume that artist IDs increment over time, then the MIN(artist_id)will be the earliest.
如果您可以假设艺术家 ID 随时间增加，那么MIN(artist_id)将是最早的。
So try something like this (untested...)
所以尝试这样的事情（未经测试......）
SELECT *
FROM feeds f
LEFT JOIN artists a ON a.artist_id = (
SELECT
MIN(fa.artist_id) a_id
FROM feeds_artists fa
WHERE fa.feed_id = f.feed_id
) a
回答by Denis Khvorostin
Version without subselect:
没有子选择的版本：
SELECT f.title,
f.content,
MIN(a.artist_name) artist_name
FROM feeds f
LEFT JOIN feeds_artists fa ON fa.feed_id = f.id
LEFT JOIN artists a ON fa.artist_id = a.artist_id
GROUP BY f.id
回答by oriadam
based on several answers here, i found something that worked for me and i wanted to generalize and explain what's going on.
基于这里的几个答案，我发现了一些对我有用的东西，我想概括和解释发生了什么。
convert:
转变：
LEFT JOIN table2 t2 ON (t2.thing = t1.thing)
to:
到：
LEFT JOIN table2 t2 ON (t2.p_key = (SELECT MIN(t2_.p_key)
FROM table2 t2_ WHERE (t2_.thing = t1.thing) LIMIT 1))
the condition that connects t1 and t2 is moved from the ONand into the inner query WHERE. the MIN(primary key)or LIMIT 1makes sure that only 1 row is returned by the inner query.
连接 t1 和 t2 的条件从ON和移动到内部查询中WHERE。在MIN(primary key)或LIMIT 1确保只有1行由内部查询返回。
after selecting one specific row we need to tell the ONwhich row it is. that's why the ONis comparing the primary key of the joined tabled.
选择一个特定的行后，我们需要告诉ON它是哪一行。这就是为什么ON要比较连接表的主键。
you can play with the inner query (i.e. order+limit) but it must return one primary key of the desired row that will tell the ONthe exact row to join.
您可以使用内部查询（即订单+限制），但它必须返回所需行的一个主键，该主键将告诉ON要加入的确切行。
回答by Ari Waisberg
I've used something else (I think better...) and want to share it:
我使用了其他东西（我认为更好......）并想分享它：
I created a VIEW that has a "group" clause
我创建了一个具有“组”子句的 VIEW
CREATE VIEW vCountries AS SELECT * PROVINCES GROUP BY country_code
SELECT * FROM client INNER JOIN vCountries on client_province = province_id
I want to say yet, that I think that we need to do this solution BECAUSE WE DID SOMETHING WRONG IN THE ANALYSIS... at least in my case... but sometimes it's cheaper to do this that to redesign everything...
我还想说，我认为我们需要做这个解决方案，因为我们在分析中做错了......至少在我的情况下......但有时这样做会更便宜，重新设计一切......
I hope it helps!
我希望它有帮助！
回答by HoldOffHunger
I want to give a more generalized answer. One that will handle any case when you want to select only the first item in a LEFT JOIN.
我想给出一个更笼统的答案。当您只想选择 LEFT JOIN 中的第一项时，它将处理任何情况。
You can use a subquery that GROUP_CONCATS what you want (sorted, too!), then just split the GROUP_CONCAT'd result and take only its first item, like so...
您可以使用 GROUP_CONCATS 您想要的子查询（也已排序！），然后只拆分 GROUP_CONCAT 的结果并仅取其第一项，就像这样......
LEFT JOIN Person ON Person.id = (
SELECT SUBSTRING_INDEX(
GROUP_CONCAT(FirstName ORDER BY FirstName DESC SEPARATOR "_" ), '_', 1)
) FROM Person
);
Since we have DESCas our ORDER BYoption, this will return a Person id for someone like "Zack". If we wanted someone with the name like "Andy", we would change ORDER BY FirstName DESCto ORDER BY FirstName ASC.
因为我们有DESC作为我们的ORDER BY选项，这将返回一个像“Zack”这样的人的个人 ID。如果我们想要一个名字像“Andy”的人，我们会将ORDER BY FirstName DESC更改为ORDER BY FirstName ASC。
This is nimble, as this places the power of ordering totally within your hands. But, after much testing, it will not scale wellin a situation with lots of users and lots of data.
这是灵活的，因为这将订购的力量完全掌握在您的手中。但是，经过多次测试，它在拥有大量用户和大量数据的情况下无法很好地扩展。
It is, however, useful in running data-intensive reports for admin.
但是，它在为管理员运行数据密集型报告时很有用。
