选择每个父母记录的儿童记录数量

Question

这就是我所拥有的：

Table: parent

| id | name |
| -- | ---- |
|  1 | foo  |
|  2 | bar  |
|  3 | baz  |

Table: child

| id | parent_id | type_id |
| -- | --------- | ------- |
|  1 |         2 |       2 |
|  2 |         2 |       2 |
|  3 |      NULL |       2 |
|  4 |         1 |       1 |
|  5 |      NULL |       2 |
|  6 |      NULL |       1 |
|  7 |         1 |       2 |
|  8 |         3 |       1 |

我想选择所有父记录，以及每个父录记录的孩子的数量：

| id | name | type_2_count |
| -- | ---- | ------------ |
|  1 | foo  |            1 |
|  2 | bar  |            2 |
|  3 | baz  |            0 |

我尝试了这一点：

SELECT p.id, name, COUNT(c.id) type_2_count
FROM parent p LEFT JOIN child c ON c.parent_id = p.id
WHERE c.type_id = 2
GROUP BY p.id;

| id | name | type_2_count |
| -- | ---- | ------------ |
|  2 | bar  |            2 |
|  1 | foo  |            1 |

但是它缺少第三个记录。

然后：

SELECT p.id, name, t.cnt type_2_count
FROM parent p LEFT JOIN (
  SELECT parent_id, COUNT(*) as cnt
  FROM child
  WHERE type_id = 2
  GROUP BY parent_id
) t ON t.parent_id = p.id;

| id | name | type_2_count |
| -- | ---- | ------------ |
|  1 | foo  |            1 |
|  2 | bar  |            2 |
|  3 | baz  |         NULL |

但是type_2_count是null而不是0对于第三个记录。

这是我使用的模式：

CREATE TABLE IF NOT EXISTS parent (
  id INT AUTO_INCREMENT,
  name VARCHAR(45) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO parent VALUES (1, 'foo'), (2, 'bar'), (3, 'baz');

CREATE TABLE IF NOT EXISTS child (
  id INT AUTO_INCREMENT,
  parent_id INT REFERENCES parent(id),
  type_id TINYINT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO child VALUES (1, 2, 2), (2, 2, 2), (3, NULL, 2), (4, 1, 1), (5, NULL, 2), (6, NULL, 1), (7, 1, 2), (8, 3, 1);

Answer 1

在您的第一个查询中，您需要的唯一更改是将条件从条款从 on on 子句中移动到 or or 条款：

SELECT p.id, name, COUNT(c.id) type_2_count
FROM parent p LEFT JOIN child c 
ON c.parent_id = p.id AND c.type_id = 2
GROUP BY p.id;

以及在您的第二张查询中使用coalesce（） /code>将null转换为0：

SELECT p.id, name, 
       COALESCE(t.cnt, 0) type_2_count
FROM parent p LEFT JOIN (
  SELECT parent_id, COUNT(*) as cnt
  FROM child
  WHERE type_id = 2
  GROUP BY parent_id
) t ON t.parent_id = p.id; 

请参阅 demo 。