如何查询共同参会次数最多的2名人员的姓名和职位并输出指定格式

问题原因 #

你现有SQL已实现两两人员共同参会次数的统计，缺失两个核心逻辑：

1. 未查询person表中的职位字段，无法输出职位信息
2. 未筛选共同参会次数最高的组合，也没有对齐要求的输出格式

修正方案 #

方案1：输出配对格式结果（两人同行，含共同参会次数）

select 
  pe1.name as 人员1姓名,
  pe1.position as 人员1职位,
  pe2.name as 人员2姓名,
  pe2.position as 人员2职位,
  count(1) as 共同参会次数
from participant pa1
join participant pa2
  on pa2.idMeeting = pa1.idMeeting
  and pa2.idPerson > pa1.idPerson -- 避免重复统计同一对人员
join person pe1
  on pe1.id = pa1.idPerson
join person pe2
  on pe2.id = pa2.idPerson
group by pe1.id, pe1.name, pe1.position, pe2.id, pe2.name, pe2.position
order by 共同参会次数 desc, pe1.name, pe2.name
limit 1; -- 仅取共同参会次数最高的一组

方案2：输出单列格式结果（两人各占一行，仅显示姓名+职位）

如果要求每个人员单独占一行输出，可用以下写法（支持MySQL 8.0+/PostgreSQL/SQL Server等支持CTE的数据库）：

with top_pair as (
  select 
    pe1.id as id1, pe1.name as name1, pe1.position as pos1,
    pe2.id as id2, pe2.name as name2, pe2.position as pos2
  from participant pa1
  join participant pa2
    on pa2.idMeeting = pa1.idMeeting
    and pa2.idPerson > pa1.idPerson
  join person pe1 on pe1.id = pa1.idPerson
  join person pe2 on pe2.id = pa2.idPerson
  group by pe1.id, pe1.name, pe1.position, pe2.id, pe2.name, pe2.position
  order by count(1) desc, pe1.name, pe2.name
  limit 1
)
select name1 as 姓名, pos1 as 职位 from top_pair
union all
select name2 as 姓名, pos2 as 职位 from top_pair;

如果你的数据库不支持CTE语法，将top_pair的逻辑替换为嵌套子查询即可实现相同效果。

内容的提问来源于stack exchange，提问作者Heer