MySQL多列关联单表查询：员工表多分类ID转名称

针对你当前的表结构，你需要多次关联Category表，分别对应Employee表中的cat1、cat2、cat3字段，就能把每个分类ID转换成对应的名称。

对应的MySQL查询语句如下：

SELECT 
    e.ID,
    e.Name,
    c1.Name AS cat1,
    c2.Name AS cat2,
    c3.Name AS cat3
FROM Employee e
LEFT JOIN Category c1 ON e.cat1 = c1.IDCAT
LEFT JOIN Category c2 ON e.cat2 = c2.IDCAT
LEFT JOIN Category c3 ON e.cat3 = c3.IDCAT;

这里用LEFT JOIN而非INNER JOIN，是为了避免因某个cat字段为空（比如第一条数据的cat3）导致整条记录被过滤，这样能保留所有员工信息，空的分类字段会显示为NULL，你在PHP里可以轻松处理成空字符串。

虽然上面的查询能解决当前问题，但你的表结构存在扩展性硬伤：如果以后员工可以拥有3个以上的分类，你就得不断新增cat4、cat5等字段，这既不符合数据库设计的第三范式，也会让查询逻辑越来越臃肿。

推荐改成多对多关联结构，新增一张中间表Employee_Category存储员工和分类的关联关系：

调整后的表结构： #

1. Category表（保持不变）：

IDCAT | Name
----------
1 | Mechanic
2 | Office
3 | Generic Mechanic

2. Employee表（移除cat1、cat2、cat3字段）：

ID | Name
----------
1 | Mechanic
2 | Office
3 | Generic Mechanic

3. Employee_Category表（新增）：

EmployeeID | CategoryID
------------------------
1 | 1
1 | 2
2 | 1
2 | 3
3 | 1
3 | 2
3 | 3

新结构下的查询方式 #

如果需要和原需求格式一致，按列展示分类名称，可以用条件聚合：

SELECT
    e.ID,
    e.Name,
    MAX(CASE WHEN ec.rank = 1 THEN c.Name END) AS cat1,
    MAX(CASE WHEN ec.rank = 2 THEN c.Name END) AS cat2,
    MAX(CASE WHEN ec.rank = 3 THEN c.Name END) AS cat3
FROM Employee e
JOIN (
    SELECT 
        EmployeeID, 
        CategoryID,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY CategoryID) AS rank
    FROM Employee_Category
) ec ON e.ID = ec.EmployeeID
JOIN Category c ON ec.CategoryID = c.IDCAT
GROUP BY e.ID, e.Name;

如果更倾向于把分类名称合并成一个字符串（比如逗号分隔），可以用GROUP_CONCAT：

SELECT
    e.ID,
    e.Name,
    GROUP_CONCAT(c.Name SEPARATOR ', ') AS categories
FROM Employee e
JOIN Employee_Category ec ON e.ID = ec.EmployeeID
JOIN Category c ON ec.CategoryID = c.IDCAT
GROUP BY e.ID, e.Name;

这种结构的优势很明显：无论员工有多少个分类，都不需要修改表结构，查询逻辑也更灵活，完全符合数据库设计的最佳实践。

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