应用如下:
假如一个人有9种权限,每种权限就是有或者没有,我想用 bit 类型保存到数据库中去,保存的结果就是1010这样的数据,1表示有权限,0表示没有。
create table TEMP
(
COLID NUMBER(20),
PERMISSION RAW(10),
userid NUMBER(20)
)INSERT INTO temp VALUES(1,'0000000101',2);
INSERT INTO temp VALUES(2,'0001000001',1) ;
INSERT INTO temp VALUES(3,'0000010000',1) ;
INSERT INTO temp VALUES(4,'0100000000',2) ;
我要合并用户id为1的用户的权限值
合并的正确结果应该是 permisson userid
0001010001 1
这个sql语句该怎么写呢?
另外 oracle中按位 做非操作 命令是哪个, 比如 ‘101’ 怎么变成 ‘010’
假如一个人有9种权限,每种权限就是有或者没有,我想用 bit 类型保存到数据库中去,保存的结果就是1010这样的数据,1表示有权限,0表示没有。
create table TEMP
(
COLID NUMBER(20),
PERMISSION RAW(10),
userid NUMBER(20)
)INSERT INTO temp VALUES(1,'0000000101',2);
INSERT INTO temp VALUES(2,'0001000001',1) ;
INSERT INTO temp VALUES(3,'0000010000',1) ;
INSERT INTO temp VALUES(4,'0100000000',2) ;
我要合并用户id为1的用户的权限值
合并的正确结果应该是 permisson userid
0001010001 1
这个sql语句该怎么写呢?
另外 oracle中按位 做非操作 命令是哪个, 比如 ‘101’ 怎么变成 ‘010’
SQL> select utl_raw.bit_or('0001000001','0000010000') as result from dual;
RESULT
--------------------------------------------------------------------------------
0001010001
SQL> select utl_raw.bit_or('0001000001','0000010001') as result from dual;
RESULT
--------------------------------------------------------------------------------
0001010001
原来真有按位或,utl_raw.bit_or,学习
我要得到这样的结果
permission userid
0001010001 1
0000000101 2
... ...就是同一列的数据 按用户分组 然后 按位合并,得到每个用户最终的权限数据。 mysql数据库一个group by就可以了: select BIN(BIT_OR(permisson )), channelid from temp group by channelid (这是mysql 数据库的语句); 但是oracle数据库 select utl_raw.bit_or(permission),channelid from temp group by channelid 这样是不行的,首先bit_or的参数个数不对,第二 这样的 group by语句好像不合法。
COLID PERMISSION USERID
--------------------- -------------------- ---------------------
1 0000000101 2
2 0001000001 1
3 0000010000 1
4 0100000000 2
5 0101000001 1
6 0101000001 1
7 0101000001 1
8 0101000001 1
9 0101000001 1
10 0101000001 1
11 0101000001 1
12 0101000001 1
13 0101000001 1
13 rows selected
-- 9i 方法
SQL> SELECT userid,
2 MAX(replace(sys_connect_by_path(max_str, ','), ',', '')) new_permission
3 FROM (select userid, rn, max(substr(permission, rn, 1)) max_str
4 from km_test_1 a,
5 (select rownum rn
6 from dual,
7 (select max(lengthb(permission)) max_len from km_test_1)
8 connect by rownum <= max_len) b
9 where rn <= lengthb(permission)
10 group by userid, rn) t
11 START WITH rn = 1
12 CONNECT BY rn = PRIOR rn + 1
13 AND userid = PRIOR userid
14 GROUP BY userid;
USERID NEW_PERMISSION
--------------------- --------------------------------------------------------------------------------
1 0101010001
2 0100000101
-- 10g 方法
SQL> select distinct userid,
2 replace(wmsys.wm_concat(max_str)
3 over(partition by userid order by userid),
4 ',',
5 '') new_permission
6 from (select userid, rn, max(substr(permission, rn, 1)) max_str
7 from km_test_1 a,
8 (select rownum rn
9 from dual,
10 (select max(lengthb(permission)) max_len from km_test_1)
11 connect by rownum <= max_len) b
12 where rn <= lengthb(permission)
13 group by userid, rn) tt;
USERID NEW_PERMISSION
--------------------- --------------------------------------------------------------------------------
1 0101010001
2 0100000101
CREATE OR REPLACE FUNCTION get_permisson(id NUMBER)
RETURN RAW
IS
Col_name RAW(20);
BEGIN
Col_name :='00000000000000000000';
FOR cur IN (SELECT PERMISSION FROM temp WHERE userid=id ) LOOP
Col_name := utl_raw.bit_or(Col_name,cur.PERMISSION);
END LOOP;
RETURN Col_name;
END;
select get_permisson_org(userid),userid from (select distinct userid from temp);--测试搞定。