Mysql子查询选择所有id

Question

所以，我需要帮助在子查询中进行选择，我知道这是一些基础知识，但我还不能自己完成。

(Reparacao=repairs 和 lojas=stores，从葡萄牙语到英语)

我想获得每家商店的维修次数。但是我一次只能做一个商店，但我想要一个选择所有商店的查询。

我正在使用这个查询:

SELECT COUNT( DISTINCT id_reparacao ) , lojas.nome
FROM reparacoes
INNER JOIN lojas ON lojas.id = 1
WHERE id_loja = lojas.id

我想要这样的东西:

SELECT COUNT( DISTINCT id_reparacao ) , lojas.nome
FROM reparacoes
INNER JOIN lojas ON lojas.id = (Select id from lojas)    //i want the id of every store
WHERE id_loja = lojas.id

Answer 1

试试这个:

无需使用子查询来获取商店的所有维修计数。您可以使用 JOIN 和 GROUP BY 轻松获得结果。

SELECT COUNT(DISTINCT id_reparacao) repairCnt, lojas.nome
FROM reparacoes
INNER JOIN lojas ON lojas.id = id_loja 
GROUP BY lojas.id