MySQL查询员工个人档案时如何调整语句避免基础信息冗余

问题原因 #

你当前的查询存在两个导致大量冗余行的问题：

1. 缺失Employee_T和Project_T的关联条件，会生成员工技能和全量项目的无效笛卡尔积
2. 员工对应多项目、多技能的一对多关联，直接拉取明细会导致基础信息随明细行重复展示

优化方案 #

方案1：聚合多值字段，单条返回所有信息（推荐） #

使用数据库自带的字符串聚合函数，把多值的项目、技能合并为单个字段，确保员工基础信息仅展示一次，以下是适配主流数据库的写法：

MySQL 写法

SELECT 
    Employee_Name, 
    Employee_Type, 
    Email, 
    Birth_Date, 
    Previous_Education,
    GROUP_CONCAT(DISTINCT Project_Name SEPARATOR ', ') AS Participated_Projects,
    GROUP_CONCAT(DISTINCT Skill_Title SEPARATOR ', ') AS Owned_Skills
FROM Employee_T
LEFT JOIN Skill_T ON Employee_T.Employee_ID = Skill_T.Employee_ID
LEFT JOIN Skill_Overview_T ON Skill_T.Skill_Code = Skill_Overview_T.Skill_Code
-- 请根据实际业务表结构调整项目表的关联条件，此处为示例逻辑
LEFT JOIN Project_T ON Employee_T.Employee_ID = Project_T.Employee_ID
WHERE Employee_Name = 'xyz'
GROUP BY Employee_Name, Employee_Type, Email, Birth_Date, Previous_Education;

PostgreSQL/SQL Server 写法

仅需将聚合函数替换为STRING_AGG即可：

SELECT 
    Employee_Name, 
    Employee_Type, 
    Email, 
    Birth_Date, 
    Previous_Education,
    STRING_AGG(DISTINCT Project_Name, ', ') AS Participated_Projects,
    STRING_AGG(DISTINCT Skill_Title, ', ') AS Owned_Skills
FROM Employee_T
LEFT JOIN Skill_T ON Employee_T.Employee_ID = Skill_T.Employee_ID
LEFT JOIN Skill_Overview_T ON Skill_T.Skill_Code = Skill_Overview_T.Skill_Code
LEFT JOIN Project_T ON Employee_T.Employee_ID = Project_T.Employee_ID
WHERE Employee_Name = 'xyz'
GROUP BY Employee_Name, Employee_Type, Email, Birth_Date, Previous_Education;

方案2：分多次查询明细 #

如果需要保留项目、技能的明细行不合并，可以拆分三次查询，完全避免冗余：

1. 查询员工基础信息

SELECT DISTINCT Employee_Name, Employee_Type, Email, Birth_Date, Previous_Education
FROM Employee_T
WHERE Employee_Name = 'xyz';

2. 查询员工参与的项目列表

SELECT DISTINCT Project_Name
FROM Employee_T
JOIN Project_T ON Employee_T.Employee_ID = Project_T.Employee_ID
WHERE Employee_Name = 'xyz';

3. 查询员工拥有的技能列表

SELECT DISTINCT Skill_Title
FROM Employee_T
JOIN Skill_T ON Employee_T.Employee_ID = Skill_T.Employee_ID
JOIN Skill_Overview_T ON Skill_T.Skill_Code = Skill_Overview_T.Skill_Code
WHERE Employee_Name = 'xyz';

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