Question

我有一张名为<条码>的表格,名称/代码为“姓名”和“名字”的一组。

我想估计在任何集团中,两个名字有多少次。

<><><>>>>>

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

www.un.org/Depts/DGACM/index_spanish.htm 预期结果

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
;

• 赋予你理想的结构。我更新了这套文件,以排除了援引的需要,即满足那些能够正确确定“不兼容”但不愿纠正这种说法的人的需要。

友情链接