MySQL 计算两列相同且不为空的地方，同一张表

Question

我有一个包含以下数据的表:

mysql> describe Post;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id     | int(11)      | NO   | MUL | NULL    |                |
| post_date   | datetime     | NO   |     | NULL    |                |
| in_reply_to | int(11)      | YES  |     | NULL    |                |
| text        | varchar(160) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

mysql> select id as "Row ID", user_id as "User ID", post_date as "Post Date", IF(in_reply_to is NULL, "None", in_reply_to) as "In Reply To Post ID:", CONCAT(LEFT(text,40),"...") as "Post Text" from Post;
+--------+---------+---------------------+----------------------+---------------------------------------------+
| Row ID | User ID | Post Date           | In Reply To Post ID: | Post Text                                   |
+--------+---------+---------------------+----------------------+---------------------------------------------+
|      1 |       1 | 2015-08-14 20:38:00 | None                 | This is the original test post that I pu... |
|      2 |       2 | 2015-08-14 20:39:00 | None                 | This is the second post that I put into ... |
|      3 |       5 | 2015-08-14 22:00:00 | 1                    | Hahaha, that post was hilarious. I canno... |
|      4 |       4 | 2015-08-14 23:00:00 | 1                    | Today I saw a cat jump off the roof, ont... |
|      5 |       4 | 2015-08-14 23:00:00 | None                 | Today I saw a cat jump off the roof, ont... |
|     27 |       1 | 2015-09-08 05:53:40 | 2                    | This is a mad reply ay...                   |
|     28 |       1 | 2015-09-08 11:24:05 | None                 | Yolo Swag...                                |
+--------+---------+---------------------+----------------------+---------------------------------------------+
7 rows in set (0.05 sec)

如果您不确定它们代表什么，每个列都有一个描述。对于这个问题，我关心的两列是 id 和 in_reply_to。

in_reply_to 是一个 NULLABLE FK 整数，它引用同一个表中的 id；如果in_reply_to为NULL，则表示该帖子为原始帖子，如果为整数值，则为回复帖子，表示该帖子的id回复。

在下面的例子中有4个原始帖子(1、2、5、28)和3个回复(3、4、27)，即3是对1的回复，4也是对1的回复，27是对 2 的回复。我正在寻找执行生成如下输出的 SQL 查询:

Num Replies 是一个 COUNT 行，表示同一表中有多少行的 in_reply_to 等于 id;如果没有对该帖子的回复，则显示 0(即没有行包含特定帖子的 id 作为它们的 in_reply_to 列。

谢谢。

解决方案(根据安德斯的回答):

mysql> SELECT   Post.id, Post.user_id, Post.post_date, Post.in_reply_to, CONCAT(LEFT(Post.text,40)),   IF(counts.count IS NULL, 0, counts.count) AS 'Num of Replies' FROM Post LEFT JOIN    (SELECT      in_reply_to AS id,      COUNT(*) AS count    FROM Post    WHERE in_reply_to IS NOT NULL    GROUP BY in_reply_to) AS counts  ON Post.id = counts.id;
+----+---------+---------------------+-------------+------------------------------------------+----------------+
| id | user_id | post_date           | in_reply_to | CONCAT(LEFT(Post.text,40))               | Num of Replies |
+----+---------+---------------------+-------------+------------------------------------------+----------------+
|  1 |       1 | 2015-08-14 20:38:00 |        NULL | This is the original test post that I pu |              2 |
|  2 |       2 | 2015-08-14 20:39:00 |        NULL | This is the second post that I put into  |              1 |
|  3 |       5 | 2015-08-14 22:00:00 |           1 | Hahaha, that post was hilarious. I canno |              0 |
|  4 |       4 | 2015-08-14 23:00:00 |           1 | Today I saw a cat jump off the roof, ont |              0 |
|  5 |       4 | 2015-08-14 23:00:00 |        NULL | Today I saw a cat jump off the roof, ont |              0 |
| 27 |       1 | 2015-09-08 05:53:40 |           2 | This is a mad reply ay                   |              0 |
| 28 |       1 | 2015-09-08 11:24:05 |        NULL | Random Text                              |              0 |
+----+---------+---------------------+-------------+------------------------------------------+----------------+
7 rows in set (0.00 sec)

Answer 1

您需要在同一个表上连接两个查询。第一个只选择所有帖子，第二个计算每个帖子的回复数。这是一个左连接，因为您想要包含没有任何回复的帖子(不会从第二个查询返回)。 IF 用于将 NULL 值转换为 0。

SELECT
  post.id,
  -- Other fields...,
  IF(counts.count IS NULL, 0, counts.count) AS count
FROM post
LEFT JOIN 
  (SELECT
     in_reply_to AS id,
     COUNT(*) AS count
   FROM post
   WHERE in_reply_to IS NOT NULL
   GROUP BY in_reply_to) AS counts
 ON post.id = counts.id

免责声明:我没有对此进行测试。