求一条SQL语句，及对位与选择项的一些讨论。

[tulinying]

问一下这首题有没有更简单方便的写法？下文是我解这题时的一些思考过程。

有这么一张表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++');

[我来逗你玩啦]

可以

[zy19870908]

牛逼呀！ 学习!

[maggie_cg]

牛逼呀！ 学习!

[valid26]

牛鼻子啊。。。

[kszpig]

sql语句总是不如存储过程来的简单

[valid29]

现在from里面可以套 select情况就简单多了

[cbsnm]

真想看看sql语句到底是怎么执行的

[wahahahahahaha2]

oracle也是用java写的吧

[fu3210]

特别那个not exist要是能到java类里面去调试就好了

[c3124]

楼主nb 版主也nb

[pngg]

学习 学习 。。。。

[小灰狼W]

我非常喜欢楼主的思路。没找到更好的解法 把语句按原逻辑改写一下

with t as
 (select sum(t1.qz) total_qz, t2.sn
    from (select course, power(2, rownum) qz
            from (select distinct course from enroll)) t1,
         enroll t2
   where t1.course = t2.course
   group by t2.sn)
select a.sn, b.sn
  from t a, t b
 where a.sn != b.sn
   and BITAND(A.total_qz, B.total_qz) = A.total_qz;

[小灰狼W]

10楼和12楼的也不错。性能上的差别需要测试才能得出结果。赞一个

[crusher395]

我想想了下，就是自己和自己做个笛卡尔积，这样写把with都去掉了，而且更容易理解：

SELECT '(' || a.SN || ',' || b.SN || ')'
FROM ENROLL a , ENROLL b
WHERE a.SN <> b.SN AND a.COURSE = b.COURSE
GROUP BY a.SN , b.SN
having count(1)=(select count(1) from enroll where a.sn=sn group by sn);

结果： (2,1) (3,1)

[傻傻的在飞]

牛逼，受教了

[u014262496]

[华而不实]

两种思路，都不错。。我咋没想到呢。。。

[tulinying]

谢谢大家啦~！ 觉得自已逻辑不是很好，理解别人的思路花了不少时间，最近在想另外一个编程问题，所以回复迟了。看明白思想了。

引用 30 楼 crusher395 的回复:

我想想了下，就是自己和自己做个笛卡尔积，这样写把with都去掉了，而且更容易理解：

SELECT '(' || a.SN || ',' || b.SN || ')'
FROM ENROLL a , ENROLL b
WHERE a.SN <> b.SN AND a.COURSE = b.COURSE
GROUP BY a.SN , b.SN
having count(1)=(select count(1) from enroll where a.sn=sn group by sn);

结果： (2,1) (3,1)

[czzgwz88888]

'sn1:学生1学号 'sn2:学生2学号 '学生2选的课都被学生1选了就返回（2，1）否则返回空 Private Function liststr(sn1 As Integer, sn2 As Integer) As String psql = "select * from ab where course in (select course from ab where sn=" & sn1 & " and course not in (select course from ab where sn=" & sn2 & "))" Set prs = pcn.excute(psql) If Not (prs.bof And prs.EOF) Then liststr = "(" & sn2 & "," & sn1 & ")" Else liststr = "" End If End Function

[愤飞的小鸭]

在路上 长见识了