使用函数提高查询效率
create table STUDENT
(
  STUNO   NUMBER not null,
  STUNAME VARCHAR2(1000),
  SCORE   NUMBER,
  constraint PK_STUDENT primary key (STUNO)
);
-- Add comments to the columns 
comment on column STUDENT.STUNO
  is '学号';
comment on column STUDENT.STUNAME
  is '姓名';
comment on column STUDENT.SCORE
  is '成绩';

insert into STUDENT (STUNO, STUNAME, SCORE)
values (1, '张三', 78);
insert into STUDENT (STUNO, STUNAME, SCORE)
values (2, '李四', 92);
insert into STUDENT (STUNO, STUNAME, SCORE)
values (3, '王五', 67);
insert into STUDENT (STUNO, STUNAME, SCORE)
values (4, '赵六', 88);
1.使用集合(低效)
SELECT STUNAME, SCORE, '优'
  FROM STUDENT
 WHERE SCORE >= 90
UNION ALL
SELECT STUNAME, SCORE, '良'
  FROM STUDENT
 WHERE SCORE >= 80
   AND SCORE < 90
UNION ALL
SELECT STUNAME, SCORE, '中'
  FROM STUDENT
 WHERE SCORE >= 60
   AND SCORE < 80
UNION ALL
SELECT STUNAME, SCORE, '差' FROM STUDENT WHERE SCORE < 60;
SQL> SELECT STUNAME, SCORE, '优'
  2    FROM STUDENT
  3   WHERE SCORE >= 90
  4  UNION ALL
  5  SELECT STUNAME, SCORE, '良'
  6    FROM STUDENT
  7   WHERE SCORE >= 80
  8     AND SCORE < 90
  9  UNION ALL
 10  SELECT STUNAME, SCORE, '中'
 11    FROM STUDENT
 12   WHERE SCORE >= 60
 13     AND SCORE < 80
 14  UNION ALL
 15  SELECT STUNAME, SCORE, '差' FROM STUDENT WHERE SCORE < 60;

STUNAME                   SCORE '
-------------------- ---------- --
李四                         92 优
赵六                         88 良
张三                         78 中
王五                         67 中
2.使用decode函数(高效)
SELECT STUNAME,
       SCORE,
       DECODE(SIGN(SCORE - 90),
              -1,
              DECODE(SIGN(SCORE - 80),
                     -1,
                     DECODE(SIGN(SCORE - 60), -1, '差', '中'),
                     '良'),
              '优')
  FROM STUDENT;

SQL> SELECT STUNAME,
  2         SCORE,
  3         DECODE(SIGN(SCORE - 90),
  4                -1,
  5                DECODE(SIGN(SCORE - 80),
  6                       -1,
  7                       DECODE(SIGN(SCORE - 60), -1, '差', '中'),
  8                       '良'),
  9                '优')
 10    FROM STUDENT;

STUNAME                   SCORE DE
-------------------- ---------- --
张三                         78 中
李四                         92 优
王五                         67 中
赵六                         88 良

DROP TABLE RESULT; create table RESULT(rq VARCHAR2(10),SHENGFU VARCHAR2(2)); insert into result values('2005-05-09','胜'); insert into result values('2005-05-09','胜'); insert into result values('2005-05-09','负'); insert into result values('2005-05-09','负'); insert into result values('2005-05-10','胜'); insert into result values('2005-05-10','负'); insert into result values('2005-05-10','负'); COMMIT; 如果要生成下列结果, 该如何写sql语句? 胜 负 2005-05-09 2 2 2005-05-10 1 2 解答: a，第一种方式 1.根据RQ进行group by 操作，即根据时间进行分组， 2.再求出同一时间胜和负的合计值 SELECT RQ, SUM(CASE WHEN SHENGFU = '胜' THEN 1 ELSE 0 END) 胜, SUM(CASE WHEN SHENGFU = '负' THEN 1 ELSE 0 END) 负 FROM RESULT GROUP BY RQ; SQL> SELECT RQ, 2 SUM(CASE 3 WHEN SHENGFU = '胜' THEN 4 1 5 ELSE 6 0 7 END) 胜, 8 SUM(CASE 9 WHEN SHENGFU = '负' THEN 10 1 11 ELSE 12 0 13 END) 负 14 FROM RESULT 15 GROUP BY RQ; RQ 胜 负 ---------- ---------- ---------- 2005-05-10 1 2 2005-05-09 2 2 b.第二种方式 1.分别查询胜的次数及其时间， 2.再把两个结果集进行内连接 SELECT N.RQ, N.胜, M.负 FROM (SELECT RQ, COUNT(*) AS 胜 FROM RESULT WHERE SHENGFU = '胜' GROUP BY RQ) N INNER JOIN (SELECT RQ, COUNT(*) AS 负 FROM RESULT WHERE SHENGFU = '负' GROUP BY RQ) M ON N.RQ = M.RQ; SQL> SELECT N.RQ, N.胜, M.负 2 FROM (SELECT RQ, COUNT(*) AS 胜 3 FROM RESULT 4 WHERE SHENGFU = '胜' 5 GROUP BY RQ) N 6 INNER JOIN (SELECT RQ, COUNT(*) AS 负 7 FROM RESULT 8 WHERE SHENGFU = '负' 9 GROUP BY RQ) M ON N.RQ = M.RQ; RQ 胜 负 ---------- ---------- ---------- 2005-05-10 1 2 2005-05-09 2 2 c.第三种方式 1.分别查询胜的次数及其时间 2、通过等值条件进行连接起来 SELECT N.RQ, N.胜, M.负 FROM (SELECT RQ, COUNT(*) AS 胜 FROM RESULT WHERE SHENGFU = '胜' GROUP BY RQ) N， (SELECT RQ, COUNT(*) AS 负 FROM RESULT WHERE SHENGFU = '负' GROUP BY RQ) M WHERE N.RQ = M.RQ; SQL> SELECT N.RQ, N.胜, M.负 2 FROM (SELECT RQ, COUNT(*) AS 胜 3 FROM RESULT 4 WHERE SHENGFU = '胜' 5 GROUP BY RQ) N， (SELECT RQ, COUNT(*) AS 负 6 FROM RESULT 7 WHERE SHENGFU = '负' 8 GROUP BY RQ) M 9 WHERE N.RQ = M.RQ; RQ 胜 负 ---------- ---------- ---------- 2005-05-10 1 2 2005-05-09 2 2