JOIN子句中的MYSQL子查询SELECT [英] MYSQL subquery SELECT in JOIN clause

问题描述

好吧...好吧，我必须将子查询放入JOIN子句中，因为它选择了多个列，而将其放在SELECT子句中则不允许这样做，因为它给我一个操作数错误. 任何人，这是我的查询:

Ok... well I have to put the subquery in a JOIN clause since it selects more than one column and putting it in the SELECT clause does not allow that as it gives me an error of an operand. Anywho, this is my query:

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

现在我在这里需要重要的事情... FOR EACH类别，我想在每个类别中显示最新的帖子和主题标题. 但是，此select语句将在foreach循环内遍历我的main_cat中找到的常规类别. 因此，有5个主要类别和3-8个子类别.这是子类别查询.但是，对于每个子类别，我都需要获取最新的帖子..但是，它仅对每个主要类别运行此SELECT查询，因此只能在所有子类别之间选择THE LATEST帖子...我想获取每个EACH子类别的最新帖子，但我宁愿不为每个子类别运行此查询...，因为我希望页面加载速度更快. 但是请记住，有些子类别将没有最新文章，因为其中一些甚至可能还没有主题！因此是左联接.

Now the important thing I need here... FOR EACH category, 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.

有人知道该怎么做吗?

并且顺便说一句，由于c.id是一个未知列，因此在子查询中出现错误(WHERE ft.cat_id = c.id).但是我试图从外部查询中引用它，所以有人也可以在这个问题上为我提供帮助吗?

AND BTW, there is an error it gives me (WHERE ft.cat_id = c.id) in the subquery because c.id is an unknown column. But I'm trying to reference it from the outer query so can someone help me on that issue as well?

谢谢！

所有表格:

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'

函数中列出了主要类别.我没有将它们存储在数据库中，因为它们从未更改，这是在浪费空间.虽然有7个主要类别.

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.

推荐答案

很难看到表的DDL，相关的示例数据和所需的输出.

It's hard to tell without seeing DDL of your tables, relevant sample data and desired output.

我可能对您的要求有误，但是请尝试以下操作:

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

这篇关于JOIN子句中的MYSQL子查询SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！