MySQL如何从引用表中转换多个值

Question

MYSQL - 鉴于我有 2 个表

tbl_tag

+------+-------------------+|  ID  |       VALUE       |+------+-------------------+| 8001 | Software Engineer |  | 8002 | C# Developer      |  | 8003 | PHP Developer     |  +------+-------------------+

员工详情

   id      name      current_pos   current_id  prev_pos         prev_id----------------------------------------------------------------------------1001    John      C# Developer       8002   Software Engineer      8001    1002    Drew      PHP Developer      8003   Software Engineer      8001    1003    Marco     C# Developer       8002   PHP Developer          8003    1004    Ashley    Software Engineer  8001   C# Developer           8002   1005    Tomas     C# Developer       8002   Software Engineer      8001    1006    Alison    PHP Developer      8003   C# Developer           8002  

我想将给定职位的 ID 插入到 employee 表中，因为职位 ID 可以从 tbl_tag 表。我只想将值的 id 插入到数据中。

这应该是输出

员工

    +------+--------+----------------+-------------+|  id  |  name  | current_pos_id | prev_pos_id |  +------+--------+----------------+-------------+| 1001 | John   |          8002  |       8001  |  | 1002 | Drew   |          8003  |       8001  |  | 1003 | Marco  |          8002  |       8003  | | 1004 | Ashley |          8001  |       8002  |  | 1005 | Tomas  |          8002  |       8001  |  | 1006 | Alison |          8003  |       8002  |  +------+--------+----------------+-------------+

到目前为止，这是我的代码....我不知道如何让它成为多个

INSERT INTO employee (name, current_pos_id ,prev_pos_id)SELECT 'Michael', tag_id as curr_pos, tag_id as prev_posFROM tbl_tag WHERE value = 'PHP Developer' 

我想插入 michael，当前职位是 PHP 开发人员，上一个职位是 C# 开发人员。谢谢你!

                                        +------+--------+----------------+-------------+|  id  |  name  | current_pos_id | prev_pos_id |  +------+--------+----------------+-------------+| 1007 | Michael|          8003  |       8002  |  +------+--------+----------------+-------------+

很抱歉我的帖子很长，我是 MYSQL 的初学者，很抱歉我的英语不是我的母语。谢谢!

Answer 1

你应该从两个表中选择..这样你就可以使用例如:交叉连接每个与你需要的工作相关的表

INSERT INTO employee (name, current_pos_id ,prev_pos_id)
SELECT 'Michael', a.tag_id as curr_pos, b.tag_id as prev_pos
FROM tbl_tag a 
CROSS JOIN bl_tag a 
WHERE a.value = 'PHP Developer' 
AND b.value = 'C# Developer'