如何将父ID为0的ID关联至含唯一父ID的ID查询？附用户讨论列表需求

First, let's lay out your posts table structure and data clearly for reference:

SQL Query to Link Parent Posts with Replies #

To associate each parent post (where PID=0) with its corresponding replies (posts that have this parent's ID as their PID), you can use a self-join on the posts table. This lets you pull together parent and child post data in one result set.

Basic Association Query (Parent + Replies)

This query returns each parent post paired with every reply under it:

SELECT
    p.id AS parent_post_id,
    p.title AS parent_title,
    p.user AS parent_author,
    c.id AS reply_post_id,
    c.title AS reply_title,
    c.user AS reply_author,
    c.fid AS forum_id
FROM posts p
INNER JOIN posts c ON p.id = c.pid
WHERE p.pid = 0;

Full Discussion List (Including Parent Posts Themselves)

If you want a complete list that includes both the parent posts and their replies (grouped by discussion), use UNION ALL to combine parent and reply records:

-- Get all parent posts
SELECT
    id AS post_id,
    title AS post_title,
    user AS post_author,
    pid AS parent_id,
    fid AS forum_id,
    'Parent' AS post_type
FROM posts
WHERE pid = 0

UNION ALL

-- Get all replies linked to parent posts
SELECT
    c.id AS post_id,
    c.title AS post_title,
    c.user AS post_author,
    c.pid AS parent_id,
    c.fid AS forum_id,
    'Reply' AS post_type
FROM posts p
INNER JOIN posts c ON p.id = c.pid
WHERE p.pid = 0

-- Sort to group discussions together, with parent first
ORDER BY parent_id, post_type, post_id;

What This Does #

• The self-join connects each parent post (p) to all its replies (c) by matching p.id to c.pid.
• The UNION ALL version gives you a flat list of all posts organized by discussion thread, making it easy to build a user-facing discussion list that shows who posted what in each thread.

内容的提问来源于stack exchange，提问作者user3502782