mysql - 每行计数函数

Question

我必须提供一份不同网站的列表，这些网站是活跃的，有一个或多个域，而且它们的域都被删除了。到目前为止，这是我的查询。

SELECT DISTINCT *
FROM sites JOIN domains ON domains.site = sites.id
WHERE domains.is_deleted = 1 AND sites.is_deleted = 0 

根据我的研究，检查站点是否具有多个域的最佳方法似乎是使用 COUNT() 子查询。如何使用 COUNT() 来计算每个站点的域数？

这是一个 SQL fiddle .

Answer 1

获取每个站点满足条件的域数:

SELECT          DISTINCT sites.id,
                sites.name,
                COUNT(domains.id) AS DomainCount
FROM            sites
INNER JOIN      domains ON domains.site = sites.id
WHERE           domains.is_deleted = 1
AND             sites.is_deleted = 0
GROUP BY        sites.id,
                sites.name

正如 karina 所说，以下查询将只显示具有多个域的站点:

SELECT          DISTINCT sites.id,
                sites.name
FROM            sites
INNER JOIN      domains ON domains.site = sites.id
WHERE           domains.is_deleted = 1
AND             sites.is_deleted = 0
GROUP BY        sites.id,
                sites.name
HAVING          COUNT(domains.id) > 1