mysql - 在关系表中搜索并获取正确的计数

Question

在此查询中:http://sqlfiddle.com/#!9/fee4c6/3/0

SELECT d.`id`, COUNT(da.`doc_id`), COUNT(db.`doc_id`)
FROM `docs` d

LEFT JOIN `docs_scod_a` da ON da.`doc_id` = d.`id`
LEFT JOIN `scod_a` a ON a.id = da.`doc_id`

LEFT JOIN `docs_scod_b` db ON db.`doc_id` = d.`id`
LEFT JOIN `scod_a` b ON b.`id` = db.`doc_id`

GROUP BY d.`id`

我应该计算每个 doc_id 及其 scod_x 具有相同 d.id = x.doc_id 的记录计数

所以，预期的结果是:

| id | COUNT(da.`doc_id`) | COUNT(db.`doc_id`) |
|----|--------------------|--------------------|
|  1 |                  3 |                  3 |
|  2 |                  3 |                  1 |
|  3 |                  2 |                  3 |
|  4 |                  0 |                  1 |

但我得到的是:

| id | COUNT(da.`doc_id`) | COUNT(db.`doc_id`) |
|----|--------------------|--------------------|
|  1 |                  9 |                  9 |
|  2 |                  3 |                  3 |
|  3 |                  6 |                  6 |
|  4 |                  0 |                  1 |

同时我想通过 IN() 子句排除记录，因此对于 WHERE a.ver_a IN ('AA') AND b.ver_b IN ('BA') http://sqlfiddle.com/#!9/fee4c6/2/0

我应该得到:

| id | COUNT(da.`doc_id`) | COUNT(db.`doc_id`) |
|----|--------------------|--------------------|
|  1 |                  3 |                  3 |

但我明白了

| id | COUNT(da.`doc_id`) | COUNT(db.`doc_id`) |
|----|--------------------|--------------------|
|  1 |                  9 |                  9 |

问题到底是什么以及如何处理？

Answer 1

我会在 SELECT 子句中使用子查询:

SELECT d.`id`,
  (SELECT COUNT(*) FROM `docs_scod_a` da WHERE da.`doc_id` = d.`id`) as cda,
  (SELECT COUNT(*) FROM `docs_scod_b` db WHERE db.`doc_id` = d.`id`) as cdb
FROM `docs` d;

演示:http://sqlfiddle.com/#!9/db8589/54

请注意，您不需要接触表 scod_a 和 scod_b。

更新

要在 scod_a 和 scod_d 表上使用条件，您需要在子查询中进行 JOIN:

SELECT d.`id`,
  (
    SELECT COUNT(*)
    FROM `docs_scod_a` da
    JOIN `scod_a` a ON a.id = da.`doc_id`
    WHERE da.`doc_id` = d.`id`
      AND a.ver_a IN ('A')
  ) as cda,
  (
    SELECT COUNT(*)
    FROM `docs_scod_b` db
    JOIN `scod_b` b ON b.`id` = db.`doc_id`
    WHERE db.`doc_id` = d.`id`
      AND b.ver_b IN ('A')
  ) as cdb
FROM `docs` d;

演示:http://sqlfiddle.com/#!9/db8589/68