mysql - 连接 ~3 个表，并在仅为一张表设置 GROUP BY 之前按 ORDER BY DESC 进行排序

Question

我正在创建一个具有以下结构的论坛:

ADMINISTRATION (category)
  - Announcements (board)
      - Lorum Ipsum (thread)
      - Lorum Ipsum (thread)
  - Support (board)
      - Lorum Ipsum (thread)
      - Lorum Ipsum (thread)

GENERAL (category)
    - Introduce Yourself (board)
        - Lorum Ipsum (thread)
        - Lorum Ipsum (thread)
    - Misc. Discussion (board)
        - Lorum Ipsum (thread)
        - Lorum Ipsum (thread)

Database table structure example(s)

"category" table
[category_id][title]

"board" table
[board_id][category_id][title]

"thread" table
[thread_id][board_id][member_id][title][date]

"member" table
[member_id][username]

HTML/PHP

# Categories are already known
foreach ($categories as $c):
    echo '<a href="/some/thing/'.$c['category_id'].'" title="#">'.htmlspecialchars($c['title']).'</a>';

    /* 
       1. Get board information associated with the current category: $c['category_id'], e.g.: board_id, title.
       2. Get number of threads associated with the board, e.g.: SELECT(COUNT(thread_id) ...) AS n_threads
       3. Get member_id from "thread" table and then use it to get the username from "member" table
       4. Get other information from "thread" table, e.g.: thread_id, title, date
    */

    #$stmt = $dbc->prepare(""); , or:
    $stmt = $dbc->query("");

    # variables needed:
    $board_id = $;
    $board_title = $;
    $n_threads = $;
    $thread_id = $;
    $thread_title = $;
    $member_id = $;
    $username = $;
    $date = $;

endforeach;

Result Example

                     ##################################################
                     # A D M I N I S T R A T I O N                    # <-- Category
                     # ---    ---       - ------- ----    ---     --- #
     Board title --> # Announcements                  Thread Title    # <-- Most recent thread
 Number of threads   #                                by Username     # <-- Member who created the thread
 assoc. w/ board --> # Threads: 5                     DD/MM/YYY HH:MM # <-- Thread created on  
                     # ---             - ------- ----             --- #
                     # Support                        Thread Title    #
                     #                                by Username     #
                     # Threads: 2                     DD/MM/YYY HH:MM # 
                     ##################################################
                     # G E N E R A L                                  #
                     # ---    ---       - ------- ----    ---     --- #
                     # Introduce Yourself             Thread Title    #
                     #                                by Username     #
                     # Threads: 4                     DD/MM/YYY HH:MM #   
                     # ---             ---- ------- -             --- #
                     # Misc. Discussion               Thread Title    #
                     #                                by Username     #
                     # Threads: 1                     DD/MM/YYY HH:MM # 
                     ##################################################

我以为我成功了，但后来我意识到我从“thread”表中提取的信息并未按与 board_id 相关的最新条目排序。

我不太确定如何创建我的 select 语句。

SELECT 
    b.board_id, 
    b.title, 
    (SELECT COUNT(t.thread_id) FROM thread t WHERE t.board_id = b.board_id) AS n_threads,
    t.member_id,
    t.title,
    (SELECT username FROM member WHERE member_id = t.member_id) AS username

FROM board b LEFT JOIN thread t ON b.board_id = t.board_id

WHERE b.category_id = 123

GROUP BY b.board_id

查询结果示例

[board_id][title        ][n_threads][member_id][title       ][username]
[       1][Announcements][        5][        1][Lorum Ips...][JSmith  ] 
[       2][Support      ][        1][        1][Nobis Eri...][JSmith  ] 

上面的语句有效，但会拉取第一个创建的记录而不是最后一个记录。我尝试了一些方法，但没有成功，似乎无法弄清楚如何定位与 board_id 关联的最后一个“线程”记录，而不创建多个内联选择语句。

有没有办法让 board_id (b.board_title) 和标题 (b.title) 按 DESC 排序，而不需要创建多个内联语句？目标是显示最后创建的并与板(与当前类别关联的板)关联的线程。

谢谢!

更新:

实际表结构

CREATE TABLE category (
    category_id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL UNIQUE,
    PRIMARY KEY (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE board (
    board_id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    category_id MEDIUMINT(8) UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL UNIQUE,
    description VARCHAR(255) NOT NULL,
    privilege_level TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (board_id),
    FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;       

CREATE TABLE thread (
    thread_id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    board_id MEDIUMINT(8) UNSIGNED NOT NULL,
    member_id MEDIUMINT(8) UNSIGNED NOT NULL,
    title VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    date DATETIME NOT NULL,
    pinned TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (thread_id),
    FOREIGN KEY (board_id) REFERENCES board (board_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE member (
    member_id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    hash VARCHAR(255) NOT NULL,
    active CHAR(32),
    date DATETIME NOT NULL,
    privilege_level TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

要获取最后一个线程，您可以使用:

SELECT b.board_id, b.title, 
       (SELECT COUNT(t.thread_id) FROM thread t WHERE t.board_id = b.board_id) AS n_threads,
       t.member_id, t.title,
       (SELECT m.username FROM member m WHERE m.member_id = t.member_id) AS username
FROM board b LEFT JOIN
     thread t
     ON b.board_id = t.board_id AND
        t.date = (SELECT MAX(t2.date) FROM thread t2 WHERE t2.board_id = t.board_id)
WHERE b.category_id = 123;

您的查询不应使用GROUP BY。