mysql - 结合2表

Question

当我有如下表状态时

id  | status    | prev_status
 1  | open      | 0
 2  | checking  | 1
 3  | reject    | 2
 4  | approve   | 2
 5  | close     | 4     

和下面的表事务

id  | no     |   date     | status_id
 1  |  IDN01 | 2018-12-01 |  1
 2  |  IDN02 | 2018-12-02 |  2
 3  |  IDN03 | 2018-12-03 |  1
 4  |  IDN04 | 2018-12-04 |  1
 5  |  IDN05 | 2018-12-05 |  4     

当我执行SELECT status_id FROM transaction WHERE id = 2“如何显示结果”时，还显示 id 2 中 status_id 的下一个状态

status_id | status 
    2     | checking
    3     | reject
    4     | approve

提前谢谢你，

标签： mysql

Answer 1

你的数据库模型有这两个单独的外键有点奇怪。无论如何，您可能正在寻找的查询是：

select c.id, c.status
from transaction t
join status c on c.id = t.status_id
where t.id = 2
union all
select p.id, p.status
from transaction t
join status p on p.prev_status = t.status_id
where t.id = 2