将Projects表Employee列的ID替换为People表对应员工姓名

Hey there! Let's tackle this problem step by step. You need to map the numeric Employee IDs in the Projects table to the actual names from the People table, right? First, let's recap your table structures and data clearly:

原表结构与数据 #

People表 #

Projects表 #

I'll cover two common scenarios for you below:

场景1：查询时显示姓名（不修改原表） #

If you just want to see employee names in your query results without altering the original Projects table, using a JOIN to link the two tables is the way to go:

SELECT 
    p.ID,
    p.Project,
    pe.Employee AS EmployeeName
FROM Projects p
JOIN People pe ON p.Employee = pe.ID;

说明： #

• The JOIN clause matches the numeric Employee ID in Projects to the ID column in People, pulling in the corresponding name.
• Your query result will look like this:

场景2：永久更新Projects表的Employee列 #

If you need to permanently replace the numeric IDs in Projects' Employee column with names, use an UPDATE ... JOIN statement. Important: Back up your data first! Updates are irreversible if something goes wrong.

MySQL/MariaDB写法： #

UPDATE Projects p
JOIN People pe ON p.Employee = pe.ID
SET p.Employee = pe.Employee;

PostgreSQL写法（PostgreSQL doesn't support direct UPDATE JOIN, so use this alternative）： #

UPDATE Projects
SET Employee = pe.Employee
FROM People pe
WHERE Projects.Employee = pe.ID::TEXT;
-- Note: If Projects.Employee is an integer type, you'll need to convert it to TEXT first to store the name string.

关键注意点： #

1. Before running the update, run the query from Scenario 1 to double-check that all IDs match correctly—this avoids mismatched names.
2. If your Projects table's Employee column is an integer type, you'll need to change it to a string type first (like VARCHAR) to store names. Here's how to do that in MySQL:

ALTER TABLE Projects MODIFY COLUMN Employee VARCHAR(50);

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