mysql - SQL - 获取每个匹配字段的 bool 值

Question

我需要有关 SQL 请求的帮助。

我有 3 张 table :

表用户

id  name
1   Jon
2   Jack
3   Bill

表格类型

id  name
1   View
2   Edit
3   Delete

表右

id  user  type
1   1     1
2   1     2
3   1     3
4   2     1
5   3     1

因此表Right包含链接的用户类型对。我需要一个获取用户名的请求，以及表 Type 中每个条目的 bool (BIT) 值，该值存在于该用户的 Right 表中。我的示例表是这样的:

Username  View  Edit  Delete
Jon       1     1     1
Jack      1     0     0 
Bill      1     0     0

提前非常感谢您!

Answer 1

未经测试:

select name,
       coalesce(select 1 from `right` where `type` = 1 and right.user = user.id, 0) as `View`,
       coalesce(select 1 from `right` where `type` = 2 and right.user = user.id, 0) as `Edit`,
       coalesce(select 1 from `right` where `type` = 3 and right.user = user.id, 0) as `Delete`
from User

或者:

select name, coalesce(RVIEW.R, 0) as `View`, coalesce(REDIT.R, 0) as `Edit`, coalesce(RDEL.R, 0) as `Delete`
from User
left join (select 1 R from `right` where `type` = 1) RVIEW on (right.user = user.id)
left join (select 1 R from `right` where `type` = 2) REDIT on (right.user = user.id)
left join (select 1 R from `right` where `type` = 3) RDEL on (right.user = user.id)