是否可以执行按位分组功能?
我在包含按位标志的表中有一个字段.例如，假设有三个标志: 4 =>阅读，2 =>写，1 =>执行，表格看起来像这样*:
I have a field in a table which contains bitwise flags. Let's say for the sake of example there are three flags: 4 => read, 2 => write, 1 => execute and the table looks like this*:
user_id | file | permissions
-----------+--------+---------------
1 | a.txt | 6 ( <-- 6 = 4 + 2 = read + write)
1 | b.txt | 4 ( <-- 4 = 4 = read)
2 | a.txt | 4
2 | c.exe | 1 ( <-- 1 = execute)
我有兴趣找到在任何记录上设置特定标志(例如:写入)的所有用户.为了在一个查询中执行此操作，我认为如果您将所有用户的权限组合在一起，您将获得一个值，即他们权限的总和":
I'm interested to find all users who have a particular flag set (eg: write) on ANY record. To do this in one query, I figured that if you OR'd all the user's permissions together you'd get a single value which is the "sum total" of their permissions:
user_id | all_perms
-----------+-------------
1 | 6 (<-- 6 | 4 = 6)
2 | 5 (<-- 4 | 1 = 5)
*我的实际表格与文件或文件权限无关，只是一个例子
有没有一种方法可以在一个语句中执行此操作?在我看来，它与带有 GROUP BY 的普通聚合函数非常相似:
Is there a way I could perform this in one statement? The way I see it, it's very similar to a normal aggregate function with GROUP BY:
SELECT user_id, SUM(permissions) as all_perms
FROM permissions
GROUP BY user_id
...但很明显，一些神奇的按位或"函数而不是 SUM.有谁知道类似的吗?
...but obviously, some magical "bitwise-or" function instead of SUM. Anyone know of anything like that?
(对于奖励积分，它在 oracle 中有效吗?)
(And for bonus points, does it work in oracle?)
推荐答案
MySQL:
SELECT user_id, BIT_OR(permissions) as all_perms
FROM permissions
GROUP BY user_id
相关文章