mysql - 相关子查询显示计数列

Question

我有以下查询，效果很好:

SELECT c.cust_id, c.cust_type_cd, c.city, count(*) as `count`
FROM customer c
INNER JOIN account a
    ON a.cust_id = c.cust_id
GROUP BY c.cust_id
HAVING `count` = 2;

结果:

+---------+--------------+---------+-------+
| cust_id | cust_type_cd | city    | count |
+---------+--------------+---------+-------+
|       2 | I            | Woburn  |     2 |
|       3 | I            | Quincy  |     2 |
|       6 | I            | Waltham |     2 |
|       8 | I            | Salem   |     2 |
|      10 | B            | Salem   |     2 |
+---------+--------------+---------+-------+

我想使用相关子查询获得相同的结果。我无法制作如上所示的“计数”列:

SELECT c.cust_id, c.cust_type_cd, c.city
FROM customer c
WHERE 2 = (
    SELECT COUNT(*)
    FROM account a
    WHERE a.cust_id = c.cust_id
);

结果:

+---------+--------------+---------+
| cust_id | cust_type_cd | city    |
+---------+--------------+---------+
|       2 | I            | Woburn  |
|       3 | I            | Quincy  |
|       6 | I            | Waltham |
|       8 | I            | Salem   |
|      10 | B            | Salem   |
+---------+--------------+---------+

我怎样才能获得与使用 INNER JOIN 的结果相同的结果并拥有一个“计数”列？

Answer 1

不确定为什么要这样做，但您还需要在 SELECT 部分指定子查询，

SELECT c.cust_id, c.cust_type_cd, c.city, (SELECT COUNT(*)
                                              FROM account a
                                              WHERE a.cust_id = c.cust_id) AS `count`
FROM customer c
WHERE 2 = (
    SELECT COUNT(*)
    FROM account a
    WHERE a.cust_id = c.cust_id
);