在SQLite视图中进行复杂选择

Question

我有两个表，其中Security保存给定NTFS文件系统扫描的访问位掩码和FileSystemRights，相当于众所周知的位掩码的字符串表示。我需要创建一个视图，该视图公开给定位掩码的预期(而不仅仅是正确的)字符串表示。问题是几个枚举值复合并包含较低值的组合，因此所需的想法是不要重复隐式值。

例如，值 1179817 (Security.Id = 24) 应仅报告 ReadAndExecute 和Sync，不包括ExecuteFile、ListDirectory、Read、ReadAttributes、ReadData、ReadExtendedAttributes、ReadPermissions和Traverse，因为它们都是ReadAndExecute的一部分(例如。读取和执行和读取==读取)。显示所有值显然是正确的，但用户只想看到非隐式值。

我迷失在SQL的约束中，无法生成一个行为像这样的连接，而没有一些糟糕的嵌套情况，这将是一场噩梦。

是否存在更好的方案办法？

FileSystemRights
================
Id   Name                            Value
--   ----                            -----
1    None                            0
2    ListDirectory                   1
3    ReadData                        1
4    WriteData                       2
5    CreateFiles                     2
6    CreateDirectories               4
7    AppendData                      4
8    ReadExtendedAttributes          8
9    WriteExtendedAttributes         16
10   ExecuteFile                     32
11   Traverse                        32
12   DeleteSubdirectoriesAndFiles    64
13   ReadAttributes                  128
14   WriteAttributes                 256
15   Write                           278
16   Delete                          65536
17   ReadPermissions                 131072
18   Read                            131209
19   ReadAndExecute                  131241
20   Modify                          197055
21   ChangePermissions               262144
22   TakeOwnership                   524288
23   Synchronize                     1048576
24   FullControl                     2032127
25   GenericAll                      268435456
26   GenericExecute                  536870912
27   GenericWrite                    1073741824
28   GenericRead                     2147483648
Security
========
Id  FileSystemRights  IdentityReference
--  ----------------  -----------------
20  2032127           BUILTINAdministrators
21  2032127           BUILTINAdministrators
22  2032127           NT AUTHORITYSYSTEM
23  268435456         CREATOR OWNER
24  1179817           BUILTINUsers
25  4                 BUILTINUsers
26  2                 BUILTINUsers

MyView
======    
SELECT s.Id AS SecurityId,
f.Name
FROM Security s
JOIN FileSystemRights f
ON CASE f.Value
WHEN 0 THEN s.FileSystemRights = f.Value
ELSE (s.FileSystemRights & f.Value) == f.Value
END
ORDER BY s.Id, f.Name;

Answer 1

将名称的实际值添加到查询中。 然后围绕该查询包装另一个查询，以筛选出作为另一个值的子集的同一条目的值：

WITH AllValues(SecurityId, Name, Value) AS (
SELECT s.Id,
f.Name,
f.Value
FROM Security s
JOIN FileSystemRights f
ON CASE f.Value
WHEN 0 THEN s.FileSystemRights = f.Value
ELSE (s.FileSystemRights & f.Value) == f.Value
END
)
SELECT SecurityId,
Name
FROM AllValues
WHERE NOT EXISTS (SELECT *
FROM AllValues AS AV2
WHERE  AV2.SecurityId = AllValues.SecurityId
AND (AV2.Value & AllValues.Value) != 0
AND  AV2.Value > AllValues.Value
)
ORDER BY 1, 2;