博客

— Start

这几个关键字有一个共同点，那就是它们一般应用于子查询中。大家对 IN 和 EXISTS 都比较熟悉，这里我就不介绍了，下面我们看一看如何使用其他几个关键字，首先，我们定义如下表：

--学生
CREATE TABLE STUDENT  
(  
ID         VARCHAR2(8),  ---学号
NAME       VARCHAR2(20), ---姓名
CLASS      VARCHAR2(20), ---班级
CHINESE    NUMBER(5),    ---语文成绩
MATH       NUMBER(5)     ---数学成绩
);
 
INSERT INTO STUDENT (ID, NAME, CLASS, CHINESE, MATH) VALUES ('20090001', '张三', '五年级A班', 80 ,90);
INSERT INTO STUDENT (ID, NAME, CLASS, CHINESE, MATH) VALUES ('20090002', '李四', '五年级A班', 60 ,75);
INSERT INTO STUDENT (ID, NAME, CLASS, CHINESE, MATH) VALUES ('20090003', '王五', '五年级A班', 90 ,95);
INSERT INTO STUDENT (ID, NAME, CLASS, CHINESE, MATH) VALUES ('20090004', '赵红', '五年级B班', 70 ,90);
INSERT INTO STUDENT (ID, NAME, CLASS, CHINESE, MATH) VALUES ('20090004', '李白', '五年级B班', 85 ,80);
INSERT INTO STUDENT (ID, NAME, CLASS, CHINESE, MATH) VALUES ('20090005', '王蓝', '五年级B班', NULL ,70);

假设现在让你查询一下，A班哪些学生的数学成绩高于B班数学成绩的最小值，怎么办？我们可以采用如下 SQL：

SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND MATH >  
(  
    SELECT MIN(MATH) FROM STUDENT WHERE CLASS='五年级B班'  
); 

除此之外，我们还可以使用SOME或ANY。注意：ANY和 SOME 的作用完全和使用方式完全相同。

SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND MATH > ANY  
(  
    SELECT MATH FROM STUDENT WHERE CLASS='五年级B班'  
); 

假设现在让你查询一下，A班哪些学生的数学成绩高于B班数学成绩的最大值，怎么办呢？我们可以采用如下SQL：

SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND MATH >  
(  
    SELECT MAX(MATH) FROM STUDENT WHERE CLASS='五年级B班'  
);  

除此之外，我们还可以使用ALL,如下SQL所示：

SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND MATH > ALL  
(  
    SELECT MATH FROM STUDENT WHERE CLASS='五年级B班'  
);

至此，我们可以总结出SOME,ANY,ALL 和 MIN, MAX 的对应关系：

> ANY(sub-qurey)  ---  > MIN(sub-qurey)  
< ANY(sub-query)  ---  < MAX(sub-qurey)  
> ALL(sub-query)  ---  > MAX(sub-qurey)  
< ALL(sub-query)  ---  < MIN(sub-qurey)

至此，你应该理解了SOME,ANY,ALL关键字的作用了吧。以上操作都针对数学成绩，如果你认为对语文成绩也执行类似的操作会得到类似的答案的话，那么你就错了。一切的原因都是因为NULL引起的。下面，我们来讨论NULL对各个关键字的影响。

---语句1  
SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND CHINESE >  
(    
    SELECT MAX(CHINESE) FROM STUDENT WHERE CLASS='五年级B班'    
);
   
---语句2  
SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND CHINESE > ALL    
(    
    SELECT CHINESE FROM STUDENT WHERE CLASS='五年级B班'    
);

通常，我们认为语句 1 和语句 2 会返回同样的结果，然而上面两条语句返回的结果却令人吃惊，语句1返回王五，语句2则什么也没返回，为什么会出现这样的情况呢？MAX 函数默认会忽略NULL值，所以语句1返回了王五。但是语句2不会忽略 NULL，任何值和 NULL 做比较都返回未知。不仅如此，下面两条语句也返回不同的结果：

---语句1  
SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND CHINESE <    
(    
    SELECT MIN(CHINESE) FROM STUDENT WHERE CLASS='五年级B班'    
);
   
---语句2  
SELECT NAME FROM STUDENT WHERE CLASS='五年级A班' AND CHINESE < ALL    
(    
    SELECT CHINESE FROM STUDENT WHERE CLASS='五年级B班'    
);

—

更多参见：

Oracle SQL 精萃

—

声明：转载请注明出处

— Last edited on 2015-02-28

— Created by ShangBo on 2015-02-28

— End