sql - 选择另外两个表中的行数

Question

我有 3 个表。我想在其中检索一些信息的主要信息和另外两个仅用于行计数的信息。

我使用了这样的请求:

SELECT A.*,
    COUNT(B.id) AS b_count
FROM A
    LEFT JOIN B on B.a_id = A.id
WHERE A.id > 50 AND B.ID < 100
GROUP BY A.id

来自 Gerry Shaw 的评论 here .它工作得很好，但只适用于一张 table 。

现在我需要为第三个 (C) 表添加行数。我试过了

SELECT A.*,
    COUNT(B.id) AS b_count
    COUNT(C.id) AS c_count
FROM A
    LEFT JOIN B on B.a_id = A.id
    LEFT JOIN C on C.a_id = A.id
GROUP BY A.id

但是，由于两个左连接，我的 b_count 和我的 c_count 是假的并且彼此相等。事实上，我的实际 b_count 和 c_count 等于 real_b_count*real_c_count。知道如何在不增加大量复杂性/子查询的情况下解决这个问题吗？

---

请求的数据样本:

Table A (primary key : id)
id    | data1 | data2
------+-------+-------
1     |  0,45 |  0,79
----------------------
2     | -2,24 | -0,25
----------------------
3     |  1,69 |  1,23

Table B (primary key : (a_id,fruit))
a_id  | fruit
------+-------
1     | apple
------+-------
1     | banana
--------------
2     | apple

Table C (primary key : (a_id,color))
a_id  | color
------+-------
2     | blue
------+-------
2     | purple
--------------
3     | blue

---

预期结果:

id    | data1 | data2 | b_count | c_count
------+-------+-------+---------+--------
1     |  0,45 |  0,79 | 2       | 0
----------------------+---------+--------
2     | -2,24 | -0,25 | 1       | 2
----------------------+---------+--------
3     |  1,69 |  1,23 | 0       | 1

Answer 1

有两种可能的解决方案。一种是在 SELECT

后面使用子查询

SELECT A.*,
    (
       SELECT COUNT(B.id) FROM B WHERE B.a_id = A.id AND B.ID < 100
    ) AS b_count,
    (
       SELECT COUNT(C.id) FROM C WHERE C.a_id = A.id
    ) AS c_count
FROM A
WHERE A.id > 50

第二个是连接在一起的两个 SQL 查询

SELECT t1.*, t2.c_count
FROM
(
    SELECT A.*,
        COUNT(B.id) AS b_count
    FROM A
        LEFT JOIN B on B.a_id = A.id
    WHERE A.id > 50 AND B.ID < 100
    GROUP BY A.id
) t1
JOIN
(
    SELECT A.*,
        COUNT(C.id) AS c_count
    FROM A
        LEFT JOIN C on C.a_id = A.id
    WHERE A.id > 50
    GROUP BY A.id
) t2 ON t1.id = t2.id

我更喜欢第二种语法，因为它清楚地向优化器表明您对 GROUP BY 感兴趣，但是，查询计划通常是相同的。