MySQL - 如何从表中获取结果，以及一个查询中有多少个连接项的计数？

Question

我有一个这样的查询:

select display_order , section_name , solution_section_id from solution_sections order by display_order

它非常基础，包含特定讨论的部分。它有效。

我想要做的是还显示每个部分中的评论数量。所以我想对评论表进行连接并计算有多少评论。

以下是其他表的架构:

mysql> describe suggested_solution_comments;
+-----------------------+----------------+------+-----+---------+----------------+
| Field                 | Type           | Null | Key | Default | Extra          |
+-----------------------+----------------+------+-----+---------+----------------+
| comment_id            | int(10)        | NO   | PRI | NULL    | auto_increment |
| problem_id            | int(10)        | NO   |     | NULL    |                |
| suggested_solution_id | int(10)        | NO   |     | NULL    |                |
| commenter_id          | int(10)        | NO   |     | NULL    |                |
| comment               | varchar(10000) | YES  |     | NULL    |                |
| solution_part         | int(3)         | NO   |     | NULL    |                |
| date                  | date           | NO   |     | NULL    |                |
| guid                  | varchar(50)    | YES  | UNI | NULL    |                |
+-----------------------+----------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

mysql> describe solution_sections;
+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| solution_section_id | int(10)       | NO   | PRI | NULL    | auto_increment |
| display_order       | int(10)       | NO   |     | NULL    |                |
| section_name        | varchar(1000) | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+

因此，它必须是solution_section_id和solution_part的联接(这些是外键，即使它们的命名有些不一致)，其中problem_id = some id。

但是我如何获得 suggest_solution_comments 表中返回评论的数量？

谢谢!

Answer 1

更新了外部连接:

select s.display_order, s.section_name, s.solution_section_id
      ,count(c.comment_id) AS comment_count
  from solution_sections s
  left outer join suggested_solution_comments c ON (c.solution_part = s.solution_section_id)
  group by s.display_order, s.section_name, s.solution_section_id
  order by display_order