JOIN 子句中的 MYSQL 子查询 SELECT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14483739/
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
MYSQL subquery SELECT in JOIN clause
提问by Peanut
Ok... well I have to put the subquery in a JOINclause since it selects more than one column and putting it in the SELECTclause does not allow that as it gives me an error of an operand.
Anywho, this is my query:
好的...好吧，我必须将子查询放在一个JOIN子句中，因为它选择了多个列并且将它放在SELECT子句中不允许这样做，因为它给了我一个操作数的错误。Anywho，这是我的查询：
SELECT
c.id,
c.title,
c.description,
c.icon,
p.id as topic_id,
p.title AS topic_title,
p.date,
p.username
FROM forum_cat c
LEFT JOIN (
SELECT
ft.id,
ft.cat_id,
ft.title,
fp.date,
u.username
FROM forum_topic ft
JOIN forum_post fp ON fp.topic_id = ft.id
JOIN user u ON u.user_id = fp.author_id
WHERE ft.cat_id = c.id
ORDER BY fp.date DESC
LIMIT 1
) p ON p.cat_id = c.id
WHERE c.main_cat = ?
ORDER BY c.list_no
Now the important thing I need here... FOR EACHcategory, I want to show the latest post and topic title in each category.
However, this select statement is going INSIDE a foreach loop looping around the general categories which is found my main_cat.
So there are 5 main categories with 3-8 subcategories.. this is the subcategory query. BUT FOR EACH subcategory, I need to grab the latest post.. However, it only runs this SELECT query for each main category so it's only select THE LATEST post between all subcategories combined... I want to get the latest post of EACH subcategory, but I rather not run this query for each subcategory... since I want the page load to be fast.
BUT REMEMBER, some subcategories WILL NOT have a latest post since some of them may not even contain a topic yet! So hence the left join.
现在我需要在这里重要的事情...FOR EACH类别，我想显示每个类别中的最新帖子和主题标题。但是，这个 select 语句将在 foreach 循环中循环，它会在我的 main_cat 中找到的一般类别周围循环。所以有 5 个主要类别和 3-8 个子类别。这是子类别查询。但是对于每个子类别，我需要获取最新的帖子.. 但是，它只对每个主类别运行此 SELECT 查询，因此它只在所有子类别组合之间选择最新的帖子......我想获得每个子类别的最新帖子，但我宁愿不为每个子类别运行此查询...因为我希望页面加载速度快。但请记住，某些子类别不会有最新帖子，因为其中一些甚至可能还没有包含主题！因此，左连接。
Does anyone know how to go about doing this?
有谁知道如何去做？
AND BTW, there is an error it gives me (WHERE ft.cat_id = c.id)in the subquery because c.idis an unknown column. But I'm trying to reference it from the outer query so can someone help me on that issue as well?
并且顺便说一句，它(WHERE ft.cat_id = c.id)在子查询中给我一个错误，因为它c.id是一个未知列。但是我试图从外部查询中引用它，所以有人也可以帮助我解决这个问题吗？
Thank you!
谢谢！
All tables:
所有表：
forum_cat (Subcategories)
-----------------------------------------------
ID, Title, Description, Icon, Main_cat, List_no
forum_topic (Topics in each subcategory)
--------------------------------------------
ID, Author_id, Cat_id, Title, Sticky, Locked
forum_post (Posts in each topic)
--------------------------------------------
ID, Topic_id, Author_id, Body, Date, Hidden'
The main categories are listed in a function. I didn't store them in the database since it was a waste of space since they never change. There are 7 main categories though.
主要类别列在一个函数中。我没有将它们存储在数据库中，因为它们永远不会改变，因此浪费了空间。虽然有7个主要类别。
回答by peterm
It's hard to tell without seeing DDL of your tables, relevant sample data and desired output.
如果不查看表的 DDL、相关示例数据和所需输出，就很难判断。
I could've got your requirements wrong, but try this:
我可能把你的要求弄错了，但试试这个：
SELECT *
FROM forum_cat c LEFT JOIN
(SELECT t.cat_id,
p.topic_id,
t.title,
p.id,
p.body,
MAX(p.`date`) AS `date`,
p.author_id,
u.username
FROM forum_post p INNER JOIN
forum_topic t ON t.id = p.topic_id INNER JOIN
`user` u ON u.user_id = p.author_id
GROUP BY t.cat_id) d ON d.cat_id = c.id
WHERE c.main_cat = 1
ORDER BY c.list_no
