-- Start
ORDER BY 子句非常简单,正因为简单,很多人不太在意,随意使用ORDER BY 子句很可能出现逻辑错误,考虑如下情况:
CREATE TABLE STUDENT
(
STUDENT_ID NUMBER(9,0),---学号
NAME VARCHAR(20) NOT NULL,---姓名
BIRTHDAY DATE---生日
);
ALTER TABLE "STUDENT" ADD CONSTRAINT "PK_STUDENT" PRIMARY KEY ("STUDENT_ID");
INSERT INTO STUDENT (STUDENT_ID,NAME,BIRTHDAY) VALUES (1,'张三',DATE '1991-01-01');
INSERT INTO STUDENT (STUDENT_ID,NAME,BIRTHDAY) VALUES (2,'李四',DATE '1991-01-01');
INSERT INTO STUDENT (STUDENT_ID,NAME,BIRTHDAY) VALUES (3,'王五',DATE '1990-01-01');
假设让你查找一下年龄最小的学生,我们很自然的会写出如下SQL:
SELECT * FROM (
SELECT * FROM STUDENT ORDER BY BIRTHDAY
) WHERE ROWNUM=1
遗憾的是,上面的语句并不总是正确的,因为张三和李四的年龄是相同的,我们应该把学号(STUDENT_ID)添加在ORDER BY 子句中,正确的SQL 如下:
SELECT * FROM (
SELECT * FROM STUDENT ORDER BY BIRTHDAY, STUDENT_ID
) WHERE ROWNUM=1
只要是ORDER BY 子句中没有主键或唯一键,就有可能出现上面的情况,所以作为一条规则,我们应该在ORDER BY 子句中加入主键或唯一键。
-- 更多参见:Oracle SQL 精萃
-- 声明:转载请注明出处
-- Last edited on 2015-06-19
-- Created by ShangBo on 2015-06-19
-- End