在SQLite中進行集合算術的最佳方法-有解無憂

成員-角色是 N:N 關系。

Member           MemberRole       Role
 ----------       ----------       ---------- 
| Id       |     | MemberId |     | Id       |
| ...      |     | RoleId   |     | Name     |
 ----------       ----------       ----------

大約有 15,000 名成員，它們的角色數量各不相同，可能為零。
大約有 50 個角色。

在資料庫之外，我有需要檢查資料庫的允許-拒絕串列。串列可能看起來像

 a -b  c  d

這意味著：

如果有角色a，
1. 允許
別的，
1. 如果有角色b，
  1. 否定
2. 別的，
  1. 如果有角色c，
    1. 允許
  2. 別的，
    1. 如果有角色d，
      1. 允許
    2. 別的，
      1. 否定

例如，具有角色a和d 的某人將被允許，而具有角色b和d 的某人將被禁止。

通過從末尾開始，對項應用并集 (∪)，對項應用差集 (-)，可以輕松地將串列轉換為數學方程-。

a -b c d ? ( ( ( ? ∪ d ) ∪ c ) - b ) ∪ a

任何尾隨的拒絕 ( -) 都可以忽略，所以我們知道第一個永遠是聯合。

a -b c d ? ( ( d ∪ c ) - b ) ∪ a

由此，我可以構建以下查詢：

       SELECT `MemberId` FROM `MemberRole` WHERE `RoleId` = @d
UNION  SELECT `MemberId` FROM `MemberRole` WHERE `RoleId` = @c
EXCEPT SELECT `MemberId` FROM `MemberRole` WHERE `RoleId` = @b
UNION  SELECT `MemberId` FROM `MemberRole` WHERE `RoleId` = @a

由于 x - y = x ∩ y'，我們也可以推匯出

a -b c d ? ( ( d ∪ c ) ∩ b' ) ∪ a

由此，我可以構建以下查詢：

SELECT `Id`
  FROM `Member`
 WHERE (
           (
               EXISTS ( SELECT * FROM `MemberRole` WHERE `MemberId` = `Member`.`Id` AND `RoleId` = @d )
               OR
               EXISTS ( SELECT * FROM `MemberRole` WHERE `MemberId` = `Member`.`Id` AND `RoleId` = @c )
           )
           AND
           NOT EXISTS ( SELECT * FROM `MemberRole` WHERE `MemberId` = `Member`.`Id` AND `RoleId` = @b )
       )
       OR
       EXISTS ( SELECT * FROM `MemberRole` WHERE `MemberId` = `Member`.`Id` AND `RoleId` = @a )

檢查成員是否被允許的最佳方法是什么？（答案通常是肯定的。）會使用WITH幫助嗎？

請注意，允許-拒絕串列的專案可以是角色 ID（數字）或角色名稱（不是數字）。

uj5u.com熱心網友回復：

這個允許規則 a -b c d可以用邏輯運算式來描述a !b(c d)。

WITH MemberRole_acl AS (
  SELECT memberId,
    SUM(roleId = 'a') AS rolesA,
    SUM(roleId = 'b') AS rolesB,
    SUM(roleId = 'c') AS rolesC,
    SUM(roleId = 'd') AS rolesD
  FROM MemberRole
  GROUP BY memberId
)
SELECT m.*
FROM Member m
JOIN MemberRole_acl r ON r.memberId = m.id
WHERE rolesA OR NOT rolesB AND (rolesC OR rolesD)

資料庫<>小提琴

uj5u.com熱心網友回復：

如果您的角色名稱為@a、@b、@c 和@d，則可以在HAVING子句中加入表、聚合和設定條件：

SELECT mr.memberid
FROM MemberRole mr INNER JOIN Role r
ON r.Id = mr.RoleId
WHERE r.Name IN (@a, @b, @c, @d)
GROUP BY mr.memberid
HAVING MAX(r.Name = @a) OR NOT MAX(r.Name = @b);

如果你有@a、@b、@c 和@d 角色的 id，那就更簡單了：

SELECT memberid
FROM MemberRole 
WHERE RoleId IN (@a, @b, @c, @d)
GROUP BY memberid
HAVING MAX(RoleId = @a) OR NOT MAX(RoleId = @b);

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/346784.html

標籤：sql sqlite 集合操作

上一篇：選擇查詢消除不需要的行

下一篇：使用SQL視窗函式計算會話開始和結束