问题描述

我在包含按位标志的表中有一个字段.例如，假设有三个标志: 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

这篇关于是否可以执行按位分组功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！