MySQL - 从每行的关联表中获取显示 COUNT() 的列

Question

我在 MySQL (5.5.60-MariaDB) 中有一个数据库。

我正在做 SELECT查询以从名为 revision_filters 的表中获取行其次是各种INNER JOIN的获取关联数据。查询如下所示并正确执行:

SELECT RevisionFilters.id      AS `RevisionFilters__id`,
       RevisionFilters.date    AS `RevisionFilters__date`,
       RevisionFilters.comment AS `RevisionFilters__comment`,
       filters.label           AS `Filters__label`,
       filters.anchor          AS `Filters__anchor`,
       groups.label            AS `Groups__label`
FROM   revision_filters RevisionFilters
       INNER JOIN dev_hub_subdb.filters Filters
               ON filters.id = ( RevisionFilters.filter_id )
       INNER JOIN dev_hub_subdb.groups Groups
               ON groups.id = ( filters.group_id )
       INNER JOIN dev_hub_subdb.regulations Regulations
               ON regulations.id = ( groups.regulation_id ) 

有一个表叫revision_filters_substances .表的结构如下。在本例中 revision_filter_id是与 revision_filters.id 相关的外键.

mysql> describe revision_filters_substances;
+--------------------+-----------------------+------+-----+---------+----------------+
| Field              | Type                  | Null | Key | Default | Extra          |
+--------------------+-----------------------+------+-----+---------+----------------+
| id                 | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| revision_filter_id | mediumint(8) unsigned | NO   | MUL | NULL    |                |
| substance_id       | mediumint(8) unsigned | NO   | MUL | NULL    |                |
+--------------------+-----------------------+------+-----+---------+----------------+

我想做的是调整我的 SELECT查询以便在返回的每一行上我都可以获得 COUNT revision_filters_substances 中的行数对应于 SELECT 中的行查询 revision_filters .

在某些情况下，revision_filters_substances 中可能没有行对应于特定的 revision_filters.id在这种情况下，我需要计数返回 0。

我读过 https://dba.stackexchange.com/questions/133384/counting-rows-from-a-subquery

但我看不出如何使它适应我的查询。

它在链接的文章上说

The subquery should immediately follow the FROM keyword.

所以我尝试在 FROM revision_filters RevisionFilters 之后立即执行此操作在查询中我已经:

, (SELECT COUNT(id) FROM revision_filters_substances WHERE id = revision_filters.id) AS count_substances 

但是这个错误:

Unknown column 'revision_filters.id' in 'where clause'

请问有人能告诉我这是否可行吗？如果也没有相应的行，我也看不到如何指定 0，因此还需要有关如何实现这一点的建议。

Answer 1

您已将 revision_filters 表别名为 RevisionFilters。在 Correlated Subquery 的 where 子句中使用 RevisionFilters.id .

此外，为了处理“无行”，当前子查询将返回NULL；你必须使用 Coalesce(..)围绕它返回 0。

SELECT .... , 
       COALESCE(SELECT COUNT(id) 
                FROM revision_filters_substances 
                WHERE id = RevisionFilters.id, 0) AS count_substances 
.... /* your rest of the query here (FROM, WHERE clauses etc) */