mysql - 多对多关系中的查询

Question

我需要一些帮助:)

我有

 ACCOUNTS
- account_id
- username
- password

USER_RIGHTS
- rights_id
- description
- level

存在多对多关系(所以我需要第三个表)USER_RIGHTS 包含以下值:

1,'READ_ACCOUNTS',0
2,'CREATE_ACCOUNTS',1
3,'UPDATE_ACCOUNTS',2
4,'DELETE_ACCOUNTS',3
5,'READ_ORDERS',0
6,'CREATE_ORDERS',1
7,'UPDATE_ORDERS',2
8,'DELETE_ORDERS',3

我需要:- 返回所有无权删除任何内容的帐户的查询- 返回具有最大用户权限的所有帐户的查询

谢谢!

Answer 1

CREATE TABLE accounts_2_user_rights
(
  id int
  account_id int
  rights_id int  
)

-- all without delete right
select *
from accounts acc
where not exists
(
  select 1
  from rights_user ru
  inner join accounts_2_user_rights a2ur
    on a2ur.rights_id = ru.rights_id
    and a2ur.account_id = acc.account_id
  where ru.description like 'DELETE%'
)

-- all maximum
select account_id, count(1)
from accounts acc
where exists(
  select 1
  from accounts_2_user_rights a2ur  
  inner join user_rights ur
    on ur.rights_id = a2ur.rights_id
  where a2ur.account_id = acc.account_id
  and count(1) = 9
)