从另一行中选择一个值：mysql

I have a table called Post:

我有一张名为Post的表：

title (NULL), content, parent_id (NULL)

The title is null because I use this for threads and replies (replies do not have a title and threads do not have a parent).

标题为null，因为我将此用于线程和回复（回复没有标题，而线程没有父级）。

Say I want to select all the replies to post x, or an n number of replies:

假设我想选择发布x的所有回复，或者n个回复：

SELECT * FROM post
WHERE title IS NULL
AND parent_id = x

or,

要么，

SELECT * FROM post
WHERE title IS NULL
AND parent_id IS NOT NULL
LIMIT 0, 30

How can I also select the title of a reply? Say for example if I select reply number 5 and it's a reply to post id# 2 (i.e has parent_id of 2), how can I select the title of number 2?

我怎样才能选择回复的标题？例如，如果我选择回复号码5并且它是对帖子ID＃2的回复（即parent_id为2），我该如何选择号码2的标题？

I don't want to use a foreach loop in mysql.

我不想在mysql中使用foreach循环。

Hope this makes sense.

希望这是有道理的。

Thank you.

谢谢。

3 个解决方案

#1

COALESCE returns first value of an argument list that is not null.

COALESCE返回非空的参数列表的第一个值。

   SELECT post.content, COALESCE(post.title, parent.title) AS title
     FROM post
LEFT JOIN post AS parent
       ON post.parent_id = parent.id
    WHERE post.parent_id = 123

#2

I'd join from your post table into your post table. It's fun. (This assumes that your post table has an id column, which corresponds to parent_id)

我会从你的邮局表加入你的邮局表。好有趣。（这假设您的post表有一个id列，对应于parent_id）

SELECT child.*, parent.title FROM post child JOIN post parent ON (child.parent_id=parent.id)

SELECT child。*，parent.title FROM post子JOIN post parent ON（child.parent_id = parent.id）

#3

Assuming your Post table structure looks like:

假设你的Post表结构如下：

+-----+------------+-----------------+----------+
| id  | parent_id  | title           | content  |
+-----+------------+-----------------+----------+
| 1   |  NULL      | Post #1 Title   | ...      |
+-----+------------+-----------------+----------+
| 2   |  NULL      | Post #2 Title   | ...      |
+-----+------------+-----------------+----------+
| 3   |   1        |                 | ...      |
+-----+------------+-----------------+----------+
| 4   |   2        |                 | ...      |
+-----+------------+-----------------+----------+

You need to use a join:

您需要使用联接：

SELECT
   *, parent.title AS parent_title
FROM
   post
LEFT JOIN
   post as parent ON parent.id = post.parent_id
WHERE
   post.id = 4

That would select post id=4 and also get you the title of post id=2 stored in the field parent_title

这将选择post id = 4并且还获得存储在字段parent_title中的post id = 2的标题

#1