sql - 选择不同的列并获取另一列的计数

Question

我有 following sqlfiddle :

CREATE TABLE tester(
  name TEXT,
  address TEXT
 )

表中的每个人可以有多个地址。我想选择所有名称和他们拥有> 1 个地址的地址数。我试过:

SELECT d.name, count(address) c FROM (SELECT DISTINCT ON(name) FROM tester) d
  LEFT JOIN tester ON d.name = links.name
  WHERE count(address) > 1

我得到:

ERROR: syntax error at or near "FROM" Position: 64

我还尝试了 DISTINCT ON 查询:

SELECT DISTINCT ON(name) name, count(address) FROM tester HAVING count(address) > 1

我得到:

ERROR: column "tester.name" must appear in the GROUP BY clause or be used in an aggregate function Position: 26

我觉得我让这件事太难了。

Answer 1

只需使用GROUP BY:

SELECT name, count(address)
FROM tester
GROUP BY name
HAVING count(address) > 1

SQL(以及其他语言)中的

GROUP BY 将始终生成不同的组，因此在这种情况下不需要 DISTINCT。