php - 一次查询查询 4 ​​个 MySQL 表

Question

我正在构建一个论坛网站，我想以以下格式向用户显示所有主题:

_________________________________________________________________________
Thread Title
Last Post: Username at Date, Started By: Username at Date
_________________________________________________________________________

这是我想出的:

         SELECT TD.id       thread_id,
                TD.sub_cat  thread_cat,
                TD.title    thread_title,
                TD.date     thread_date,
                TD.status   thread_status,
                TD.stick    thread_stick,

                US.username user,

                GR.color group_color

        FROM fr_thread AS TD
        INNER JOIN user AS US ON TD.user_id = US.id
        INNER JOIN user_group AS GR ON US.user_group = GR.id
        ORDER BY TD.stick

当我显示所有线程时，我还希望根据用户所在的用户组对用户名进行着色。因此，我还必须对此进行查询。

尽管我已经提出了大部分查询，我仍然困惑于如何查询用户名、组颜色、最后一张海报的日期。

我的数据库设计大致如下:

user:
id
user_group

user_group:
id
color

fr_thread:
id
sub_cat
user_id
date

fr_reply:
id
thread_id
user_id
date

Answer 1

尝试

SELECT  

    TD.id as thread_id , 
    TD.sub_cat as thread_sub_cat , 
    TD.user_id as thread_started_user_id , 
    TD.date as thread_started_on , 

    US1.name as thread_author , 
    US2.id as last_reply_user_id , 
    US2.name as last_reply_user_name , 

    USG1.color as thread_author_color ,  
    USG2.color as lat_reply_user_color , 

    RE.date as last_reply_date 

FROM  

    fr_thread TD join user US1 on TD.user_id = US1.id

    join user_group USG1 on USG1.id = US1.user_group 

    join fr_reply RE on RE.thread_id = TD.id

    join user US2 on US2.id = RE.user_id 

    join user_group USG2 on USG2.id = US2.user_group

为 user 表创建了 2 个别名，分别为 US1 和 US2，并且 涉及 2 个别名(创建者和最后回复者)用户组

http://sqlfiddle.com/#!9/951c0b/1