如何统计同一个表中名字之间的关系？

Question

我有一个名为 names 的表，其中的行由名称和该名称的组组成。

我想计算任何两个名字在任何组中一起出现的次数。

表格

name    | group
--------+------
Renato  |   1
John    |   1 
Paul    |   1
Renato  |   2
John    |   2
John    |   3
Paul    |   3 

预期结果

name 1  | name 2 |count
--------+--------+-----
Renato  | John   |  2
Renato  | Paul   |  1
John    | Renato |  2 
John    | Paul   |  2
Paul    | Renato |  1
Paul    | John   |  2

我该如何做到这一点？

Answer 1

SELECT n1.name AS "name 1", n2.name AS "name 2",
    COUNT(*) AS "count"
FROM "names" AS n1
JOIN "names" AS n2
    ON n1.group = n2.group
WHERE n1.name <> n2.name
GROUP BY n1.name, n2.name

Answer 2

SELECT 
  lefttbl.name as name_1,
  righttbl.name as name_2,
  count(*) as together_count
FROM
  xx AS lefttbl
  INNER JOIN xx AS righttbl on lefttbl.group=righttbl.group
WHERE lefttbl.name<>righttbl.name
GROUP BY name_1,name_2
;

为您提供所需的结构。我更新了 SQL，删除了对引号的需要，以满足那些能够正确识别引用不兼容但不愿意纠正它的人的需求。