Mysql - 如何根据第一个表的 2 列中的值显示第二个表中的列？

Question

我有两张 table ，措施:

id        currentgroup  nextgroup
1         1             2
2         2             5
3         3             24
4         99            NULL

描述:

id       desc
1        First Level
2        Second Level
3        Third Level
4        Supervisor
5        Team Leader
.
.
99       CEO

我想要这个输出

id       currentgroup   nextgroup
1        First Level    Second Level
2        Second Level   Team Leader
3        Third Level    DB department
4        CEO

这些是我尝试过的查询。要显示前两列，此列正在工作:

SELECT id, description.desc
FROM measure 
INNER JOIN description ON measure.currentgroup=description.desc
WHERE measure.id IN (1,2,3,4);

问题:我无法添加第二列。我不知道如何为第二列(nextgrouplevel)定义description.desc。我添加了第二个连接条件，但如何显示第二列“nextgroup”？

SELECT id, description.desc
FROM measure 
INNER JOIN description ON measure.currentgroup=description.desc AND measure.nextgroup=description.desc
WHERE measure.id IN (1,2,3,4);

Answer 1

您可以加入两次，每个组一次。由于您有一些没有下一组的度量，因此第二个连接应该是左连接。

select 
    m.id,
    d1.desc current_group,
    d2.desc next_group
from 
    measure m
    inner join description d1 on d1.id = m.current_group
    left  join description d2 on d2.id = m.next_group
where m.id in (1, 2, 3, 4)