mysql - 如何计算每个地址的商店数量？

Question

我有一个查询，可以查找具有重复街道名称的行:

SELECT a.id, a.street1, a.postal_code, a.fk_countryID
FROM address a
INNER JOIN (
  SELECT b.street1, b.postal_code, b.fk_countryID
  FROM address b
  GROUP BY b.street1, b.postal_code
  HAVING count(b.id) > 1
) duplicate ON a.street1 = duplicate.street1 
AND a.postal_code = duplicate.postal_code
AND a.fk_countryID = duplicate.fk_countryID
ORDER BY a.street1, a.postal_code 

结果是(忽略 NumStores 列):

+-------+-----------------+------------+------+-----+-----------+
| id    | street          | postalCode | city | CC  | NumStores |
+-------+-----------------+------------+------+-----+-----------+
| 11101 | Bogstadveien 36 | 0366       | Oslo | NO  |     ?     |
| 11102 | Bogstadveien 36 | 0366       | Oslo | NO  |     ?     |
| 11103 | Bogstadveien 36 | 0366       | Oslo | NO  |     ?     |
+---------------------------------------------------+-----------+

目标是删除重复的行。为了完成这项工作，我需要查看有多少商店实际使用每个地址。计数为 0 的我可以立即删除。那些有1个或更多的我必须在删除之前“移动”到我想要使用的地址。这都是通过 GUI (JS/PHP) 手动完成的。

所以问题是；如何添加每行的商店数量？

Answer 1

我终于找到了有用的东西。

SELECT a.id, a.street1, a.postal_code, a.fk_countryID, COUNT(c.fk_addressID) as NumStores
(...)
LEFT JOIN store c
ON a.id = c.fk_addressID
GROUP BY a.id
ORDER BY a.street1, a.postal_code; 

结果是:

+-------+-----------------+------------+------+-----+-----------+
| id    | street          | postalCode | city | CC  | NumStores |
+-------+-----------------+------------+------+-----+-----------+
| 11101 | Bogstadveien 36 | 0366       | Oslo | NO  |     0     |
| 11102 | Bogstadveien 36 | 0366       | Oslo | NO  |     0     |
| 11103 | Bogstadveien 36 | 0366       | Oslo | NO  |     3     |
+---------------------------------------------------+-----------+