oracle 是否可以执行按位分组功能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1397913/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to perform a bitwise group function?
提问by nickf
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 => executeand the table looks like this*:
我在包含按位标志的表中有一个字段。举例来说，假设有三个标志：4 => read, 2 => write, 1 => execute表格如下所示*：
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)
*My actual table isn't to do with files or file permissions, 'tis but an example
*我的实际表与文件或文件权限无关，这只是一个例子
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:
有没有一种方法可以在一个语句中执行此操作？在我看来，它与带有 GROUP BY 的普通聚合函数非常相似：
SELECT user_id, SUM(permissions) as all_perms
FROM permissions
GROUP BY user_id
...but obviously, some magical "bitwise-or" function instead of SUM. Anyone know of anything like that?
...但显然，一些神奇的“按位或”函数而不是 SUM。任何人都知道这样的事情吗？
(And for bonus points, does it work in oracle?)
（对于奖励积分，它在 oracle 中有效吗？）
回答by manji
MySQL:
MySQL：
SELECT user_id, BIT_OR(permissions) as all_perms
FROM permissions
GROUP BY user_id
回答by nickf
Ah, another one of those questions where I find the answer 5 minutes after asking... Accepted answer will go to the MySQL implementation though...
啊，我在问 5 分钟后找到答案的另一个问题......接受的答案将转到 MySQL 实现......
Here's how to do it with Oracle, as I discovered on Radino's blog
正如我在Radino 的博客上发现的那样，这是使用 Oracle的方法
You create an object...
你创建一个对象...
CREATE OR REPLACE TYPE bitor_impl AS OBJECT
(
bitor NUMBER,
STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT bitor_impl) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT bitor_impl,
VALUE IN NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT bitor_impl,
ctx2 IN bitor_impl) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(SELF IN OUT bitor_impl,
returnvalue OUT NUMBER,
flags IN NUMBER) RETURN NUMBER
)
/
CREATE OR REPLACE TYPE BODY bitor_impl IS
STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT bitor_impl) RETURN NUMBER IS
BEGIN
ctx := bitor_impl(0);
RETURN ODCIConst.Success;
END ODCIAggregateInitialize;
MEMBER FUNCTION ODCIAggregateIterate(SELF IN OUT bitor_impl,
VALUE IN NUMBER) RETURN NUMBER IS
BEGIN
SELF.bitor := SELF.bitor + VALUE - bitand(SELF.bitor, VALUE);
RETURN ODCIConst.Success;
END ODCIAggregateIterate;
MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT bitor_impl,
ctx2 IN bitor_impl) RETURN NUMBER IS
BEGIN
SELF.bitor := SELF.bitor + ctx2.bitor - bitand(SELF.bitor, ctx2.bitor);
RETURN ODCIConst.Success;
END ODCIAggregateMerge;
MEMBER FUNCTION ODCIAggregateTerminate(SELF IN OUT bitor_impl,
returnvalue OUT NUMBER,
flags IN NUMBER) RETURN NUMBER IS
BEGIN
returnvalue := SELF.bitor;
RETURN ODCIConst.Success;
END ODCIAggregateTerminate;
END;
/
...and then define your own aggregate function
...然后定义自己的聚合函数
CREATE OR REPLACE FUNCTION bitoragg(x IN NUMBER) RETURN NUMBER
PARALLEL_ENABLE
AGGREGATE USING bitor_impl;
/
Usage:
用法：
SELECT user_id, bitoragg(permissions) FROM perms GROUP BY user_id
回答by cagcowboy
And you can do a bitwise or with...
你可以按位或...
FUNCTION BITOR(x IN NUMBER, y IN NUMBER)
RETURN NUMBER
AS
BEGIN
RETURN x + y - BITAND(x,y);
END;
回答by Josh Bode
You would need to know the possible permission components (1, 2 and 4) apriori (thus harder to maintain), but this is pretty simple and would work:
您需要先验地知道可能的权限组件（1、2 和 4）（因此更难维护），但这非常简单并且可以工作：
SELECT user_id,
MAX(BITAND(permissions, 1)) +
MAX(BITAND(permissions, 2)) +
MAX(BITAND(permissions, 4)) all_perms
FROM permissions
GROUP BY user_id
回答by Lieven Keersmaekers
I'm interested to find all users who have a particular flag set (eg: write) on ANY record
我有兴趣找到在任何记录上设置特定标志（例如：写入）的所有用户
What's wrong with simply
简单有什么问题
SELECT DISTINCT User_ID
FROM Permissions
WHERE permissions & 2 = 2
