mySQL 显示具有多次出现的公共(public)属性的行

Question

我在以特定方式显示数据时遇到了一些问题。

我想展示 3 个或更多技能的共同机制。

我不确定如何计算常用技能，我尝试使用 EXIST 和子查询但无法弄清楚。

这是我必须显示所有具有他们技能的机械师的查询

Select distinct fName, lName , Skill  from Person 
inner join Worker on Person.personID = Worker.personID
inner join Mechanic on Worker.personID = Mechanic.personID
inner join Skills on Mechanic.personID = Skills.personID
order by fName

显示

fName   lName   Skill
----------------------------
Carl    Marx    fuel systems
Jimmy   John    heating and cooling systems
Jimmy   John    exhaust systems
Jimmy   John    fluid and filter service
Mike    Hawk    resurface rotors and drums
Mike    Hawk    struts and suspension
Mike    Hawk    engine performance (tune-up)
Mike    Hawk    wheel alignment
Ricky   Bobby   wheel alignment
Ricky   Bobby   resurface rotors and drums
Ricky   Bobby   A/C systems
Ricky   Bobby   struts and suspension
Ricky   Bobby   heating and cooling systems
Terry   Cruz    sound systems
Terry   Cruz    cruise control

输出应该是

Ricky   Bobby
Mike    Hawk

这是关系图

提前致谢!让我知道是否可以提供其他任何东西

Answer 1

SQL DEMO

SELECT `fNameA`, `lNameA`, `fNameB`, `lNameB`, GROUP_CONCAT(`SkillA`, ' ') as Skills
FROM (
        SELECT  DISTINCT 
                CASE WHEN M1.`id` < M2.`id` THEN M1.`fName` ELSE M2.`fName` END as `fNameA`,
                CASE WHEN M1.`id` < M2.`id` THEN M1.`lName` ELSE M2.`lName` END as `lNameA`,
                CASE WHEN M1.`id` < M2.`id` THEN M2.`fName` ELSE M1.`fName` END as `fNameB`,
                CASE WHEN M1.`id` < M2.`id` THEN M2.`fName` ELSE M1.`fName` END as `lNameB`,
                M1.`Skill` as `SkillA`, M2.`Skill` as `SkillB`
        FROM Mechanic M1
        JOIN Mechanic M2
          ON M1.`id` <> M2.`id`
         AND M1.`Skill` = M2.`Skill`  
        ORDER BY M1.`id`, M2.`id`) T
GROUP BY `fNameA`, `lNameA`, `fNameB`, `lNameB`
HAVING COUNT(*) >= 3;

简单版 只打印前两个字段

SELECT  M1.`fName`, M1.`lName`, M2.`fName`, M2.`lName`
FROM Mechanic M1
JOIN Mechanic M2
  ON M1.`id` <> M2.`id`
 AND M1.`Skill` = M2.`Skill` 
GROUP BY M1.`fName`, M1.`lName`, M2.`fName`, M2.`lName`
HAVING COUNT(*) >= 3;

输出

• 部分结果
• 带有技能列表的第一个版本
• 简单版