# SQL趣题：分组按位求或

SQL> create table table_bit (id number(6,0),bitnum varchar2(10));

SQL> insert into table_bit values(1,'001');

SQL> insert into table_bit values(1,'010');

SQL> insert into table_bit values(1,'100');

SQL> insert into table_bit values(2,'000');

SQL> insert into table_bit values(2,'001');

SQL> commit;

SQL> select * from table_bit;
ID BITNUM
---------- ----------
2 001
1 100
2 100
1 110
1 001
1 010
1 100
2 000
2 001

SQL> COL merge_bit FOR A20
SQL> WITH t AS
2    (SELECT rownum rn,
3      tb.id,
4      tb.bitnum,
5      to_number(SUBSTR(tb.bitnum,1,1)) b1,
6      to_number(SUBSTR(tb.bitnum,2,1)) b2,
7      to_number(SUBSTR(tb.bitnum,3,1)) b3
8    FROM table_bit tb
9    )
10  SELECT t.id,
11    DECODE(SIGN(SUM(t.b1)),1,1,0)
12    ||DECODE(SIGN(SUM(t.b2)),1,1,0)
13    ||DECODE(SIGN(SUM(t.b3)),1,1,0) merge_bit
14  FROM t
15  GROUP BY t.id
16  /
ID MERGE_BIT
---------- --------------------
1 111
2 001
SQL>

SQL> SELECT id,
3  FROM
4    (SELECT DISTINCT id, bitnum FROM table_bit
5    )  t
6  GROUP BY id
7  /
---------- ------
1 221
2 101

SQL>

• 博文量
35
• 访问量
95884