SQL语句中位与选择项的使用

有这么一张表enroll，表示学生的选课情况，学生2选的课程是JAVA、PYTHON，学生1选的课程是JAVA、PYTHON、C，学生2选的课都被学生1选了，就打印出（2，1），本例中符合答案的还有（3，1），用一条SQL把结果打印出来。

答案是

（2，1）

（3，1）

下面是我的解题过程和思路：

我们先定义数据选择项。

把JAVA、PHTHON、C、C++分别定义成1、2、4、8（如有更多则继续，它们是2的0次方到N次方），

在这些数据里

经过处理，每个学生的总值就变成如下，我们暂且叫这个值为“权重”。

1，"JAVA+PYTHON+C" (1+2+4=7)

1——7；

2——3；

3——5；

4——9；

BITAND函数：ORACLE内置函数，求与。比方说BITAND(3,7)=3，3的二进制位是011，7是111，相与一下是011。

下面代码中最核心的就是这条IF (BITAND(I.QZ, J.QZ) = I.QZ)，如果值为真，则表明，学生I所选的课程，学生J都选了（学生I的选课是学生J的子集）

DECLARE

BEGIN

  FOR I IN (SELECT SN,SUM(DECODE(COURSE,'JAVA',1,'PYTHON',2,'C','4','C++',8)) QZ FROM ENROLL

             GROUP BY SN ORDER BY QZ) LOOP

    FOR J IN (SELECT SN,SUM(DECODE(COURSE,'JAVA',1,'PYTHON',2,'C','4','C++',8)) QZ FROM ENROLL

               WHERE SN <> I.SN GROUP BY SN ORDER BY QZ) LOOP

      IF (BITAND(I.QZ, J.QZ) = I.QZ) THEN

        DBMS_OUTPUT.PUT_LINE('('||I.SN||','||J.SN||')');

      END IF;

    END LOOP;

  END LOOP;

END;

昨天晚上睡觉前又想了想，没想出整条的SQL应该如何写。第二天中午吃饭的时候一琢磨，我去，不就是这样子吗：

SELECT '(' || A.SN || ',' || B.SN || ')'

  FROM (SELECT SN,

               SUM(DECODE(COURSE, 'JAVA', 1, 'PYTHON', 2, 'C', 4, 'C++', 8)) QZ

          FROM ENROLL

         GROUP BY SN) A,

       (SELECT SN,

               SUM(DECODE(COURSE, 'JAVA', 1, 'PYTHON', 2, 'C', 4, 'C++', 8)) QZ

          FROM ENROLL

         GROUP BY SN) B

WHERE A.SN <> B.SN

   AND BITAND(A.QZ, B.QZ) = A.QZ

但是这条SQL语句的扩展性不好啊!

刚才下班回来想了下，要通过构建一个规则子表来。代码如下：

SELECT '(' || A.SN || ',' || B.SN || ')'

  FROM (SELECT SN,

               SUM((SELECT QZ

                     FROM (SELECT COURSE, POWER(2, ROWNUM - 1) QZ

                             FROM (SELECT DISTINCT (COURSE) COURSE FROM ENROLL)) RULES

                    WHERE RULES.COURSE = ENROLL.COURSE)) QZ

          FROM ENROLL

         GROUP BY SN) A,

       (SELECT SN,

               SUM((SELECT QZ

                     FROM (SELECT COURSE, POWER(2, ROWNUM - 1) QZ

                             FROM (SELECT DISTINCT (COURSE) COURSE FROM ENROLL)) RULES

                    WHERE RULES.COURSE = ENROLL.COURSE)) QZ

          FROM ENROLL

         GROUP BY SN) B

WHERE A.SN <> B.SN

   AND BITAND(A.QZ, B.QZ) = A.QZ;

（RULES表将课程权重定义为1、2、4等等）

PS：

数据选择项（这个东西是我在工作的时候在建模工具里看到的，我在写这个SQL时想了很长时间，想出不一条SQL来解决的方法，只好写非SQL的代码块了，你们以前做算法题训练的有接触过 数据选择项 这种思路没？），你们做项目的时候有用到过位与选择项没？（在我们项目里头，举个例子，一个员工有投顾从业资格，期货从业资格，其从业资格字段的值就是32+64=96）

脚本：

CREATE TABLE ENROLL(SN NUMBER,COURSE VARCHAR2(20));

insert into enroll (SN, COURSE)

values (1, 'JAVA');

insert into enroll (SN, COURSE)

values (1, 'PYTHON');

insert into enroll (SN, COURSE)

values (1, 'C');

insert into enroll (SN, COURSE)

values (2, 'JAVA');

insert into enroll (SN, COURSE)

values (2, 'PYTHON');

insert into enroll (SN, COURSE)

values (3, 'JAVA');

insert into enroll (SN, COURSE)

values (3, 'C');

insert into enroll (SN, COURSE)

values (4, 'JAVA');

insert into enroll (SN, COURSE)

values (4, 'C++');