mysql - 从引用表中选择值

Question

我有一个users表并具有诸如 admin|manager|employee 之类的角色其在名为 reference 的引用表中表示.

用户表 - id|first_name|last_name|type|status

引用表 - id|table|type|key|value

现在引用表包含与值匹配的整数键，因此 user.type有0-admin,1-manager,2-employee看起来像这样

table:user
type:type
key:0
value:admin

我的问题是当我必须在需要访问引用表的表中获取值时。

table:user
type:status
key:0
value:enabled

问题:如何在一条语句中访问两个引用表值？

//声明

SELECT a.id,a.first_name,a.last_name,b.value as user_type,b.value as user_status
FROM user AS a 
JOIN reference as b 
ON 'user'=b.table AND 'type'=b.type AND a.type = b.value AND a.status = b.value

Answer 1

您可以连接到引用表两次(或三次、四次...)。只需给它两个不同的别名即可:

SELECT a.id,a.first_name,a.last_name,b.value as user_type,b.value AS user_type, b2.value as user_status
FROM user AS a 
JOIN reference AS b 
ON 'user'=b.table AND 'type'=b.type AND a.type = b.value 
JOIN reference AS b2
ON 'user'=b2.table AND 'status'=b2.type AND a.status = b2.value

除非我误解了您的要求，否则我相信以上就是您所寻求的。