SQL语句专题

最新推荐文章于 2024-08-03 09:00:00 发布

Isa1_2_3

最新推荐文章于 2024-08-03 09:00:00 发布

阅读量570

点赞数

分类专栏： ORACLE

本文链接：https://blog.csdn.net/Z_Y_J_1_2_3/article/details/23601011

版权

ORACLE 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

概述：前段时，不就间看了一个牛人的博客，来检查一下SQL的能力的文章，刚开始觉得没什么几张

简单表吗？其实最后看了看就从这么4张简单表中竟然能挖掘出那么多的SQL语句。而且有些SQL实话说

还真有点不好搞。那篇博客是基于SQL SERVER的，我从新来用ORACLE实现一次，且我会尽量想多种实现方式

去写，需要的的话，可以比较他们之间的性能差异。

说明：本节只是针对查询，表我已经建好了，数据也准备好了。只需要执行以下语句就好了。

然后就可以把自己的SQL运行一下查看结果和我的进行比较。不要删除和修改我给出的初始

数据。

--注意：大写为ORACLE关键字

CREATE TABLE test_z_student(

s# VARCHAR2(20),

sname VARCHAR2(20),

sage int,

ssex VARCHAR2(20)

);

CREATE TABLE test_z_course(

c# VARCHAR2(20),

cname VARCHAR2(20),

t# VARCHAR2(20)

);

CREATE TABLE test_z_sc(

s# VARCHAR2(20), --学生ID

c# VARCHAR2(20), --课程ID

score int --分数

);

CREATE TABLE test_z_teacher(

t# VARCHAR2(20), --教师D

tname VARCHAR2(20) --教师姓名

);

---向学生表中插入数据

INSERT INTO test_z_student VALUES('stu001','stu001-name',15,'F');

INSERT INTO test_z_student VALUES('stu002','stu002-name',21,'M');

INSERT INTO test_z_student VALUES('stu003','stu003-name',20,'F');

INSERT INTO test_z_student VALUES('stu004','stu004-name',30,'M');

INSERT INTO test_z_student VALUES('stu005','stu005-name',18,'M');

INSERT INTO test_z_student VALUES('stu006','stu006-name',25,'F');

INSERT INTO test_z_student VALUES('stu007','stu007-name',26,'F');

INSERT INTO test_z_student VALUES('stu008','stu008-name',32,'M');

INSERT INTO test_z_student VALUES('stu009','stu009-name',24,'M');

INSERT INTO test_z_student VALUES('stu010','stuss-name',24,'M');

--向教师表中插入数据

INSERT INTO test_z_teacher VALUES('tcha-1','tcha-1-name');

INSERT INTO test_z_teacher VALUES('tcha-2','tcha-2-name');

INSERT INTO test_z_teacher VALUES('tcha-3','tcha-3-name');

INSERT INTO test_z_teacher VALUES('tchb-1','tchb-1-name');

INSERT INTO test_z_teacher VALUES('tchb-2','tchb-2-name');

INSERT INTO test_z_teacher VALUES('tchc-1','tchc-1-name');

INSERT INTO test_z_teacher VALUES('tchd-1','tchd-1-name');

INSERT INTO test_z_teacher VALUES('tchd-2','tchd-2-name');

--向课程表中插入数据

INSERT INTO test_z_course VALUES('c1','c1-name','tcha-1');

INSERT INTO test_z_course VALUES('c2','c2-name','tcha-2');

INSERT INTO test_z_course VALUES('c3','c3-name','tcha-3');

INSERT INTO test_z_course VALUES('c4','c4-name','tchb-1');

INSERT INTO test_z_course VALUES('c5','c5-name','tchb-2');

INSERT INTO test_z_course VALUES('c6','c6-name','tchc-1');

INSERT INTO test_z_course VALUES('c7','c7-name','tchd-1');

INSERT INTO test_z_course VALUES('c8','c8-name','tchd-2');

INSERT INTO test_z_course VALUES('c9','c9-name','tchb-1');

--向学生课程中间表中插入数据

INSERT INTO test_z_sc VALUES('stu001','c1',65);

INSERT INTO test_z_sc VALUES('stu001','c2',50);

INSERT INTO test_z_sc VALUES('stu001','c3',86);

INSERT INTO test_z_sc VALUES('stu001','c4',72);

INSERT INTO test_z_sc VALUES('stu001','c5',12);

INSERT INTO test_z_sc VALUES('stu001','c6',45);

INSERT INTO test_z_sc VALUES('stu001','c7',74);

INSERT INTO test_z_sc VALUES('stu001','c8',47);

INSERT INTO test_z_sc VALUES('stu001','c9',45);

INSERT INTO test_z_sc VALUES('stu002','c1',22);

INSERT INTO test_z_sc VALUES('stu002','c2',50);

INSERT INTO test_z_sc VALUES('stu002','c3',32);

INSERT INTO test_z_sc VALUES('stu002','c4',89);

INSERT INTO test_z_sc VALUES('stu002','c5',78);

INSERT INTO test_z_sc VALUES('stu002','c6',54);

INSERT INTO test_z_sc VALUES('stu002','c7',13);

INSERT INTO test_z_sc VALUES('stu002','c8',65);

INSERT INTO test_z_sc VALUES('stu003','c1',85);

INSERT INTO test_z_sc VALUES('stu003','c2',78);

INSERT INTO test_z_sc VALUES('stu003','c3',69);

INSERT INTO test_z_sc VALUES('stu003','c4',63);

INSERT INTO test_z_sc VALUES('stu003','c5',45);

INSERT INTO test_z_sc VALUES('stu003','c6',85);

INSERT INTO test_z_sc VALUES('stu003','c7',96);

INSERT INTO test_z_sc VALUES('stu003','c8',21);

INSERT INTO test_z_sc VALUES('stu003','c9',37);

INSERT INTO test_z_sc VALUES('stu004','c1',68);

INSERT INTO test_z_sc VALUES('stu004','c2',92);

INSERT INTO test_z_sc VALUES('stu004','c3',64);

INSERT INTO test_z_sc VALUES('stu004','c4',31);

INSERT INTO test_z_sc VALUES('stu004','c5',78);

INSERT INTO test_z_sc VALUES('stu004','c6',55);

INSERT INTO test_z_sc VALUES('stu004','c7',96);

INSERT INTO test_z_sc VALUES('stu004','c8',35);

INSERT INTO test_z_sc VALUES('stu005','c1',36);

INSERT INTO test_z_sc VALUES('stu005','c2',93);

INSERT INTO test_z_sc VALUES('stu005','c3',66);

INSERT INTO test_z_sc VALUES('stu005','c4',87);

INSERT INTO test_z_sc VALUES('stu005','c5',23);

INSERT INTO test_z_sc VALUES('stu005','c6',52);

INSERT INTO test_z_sc VALUES('stu005','c7',85);

INSERT INTO test_z_sc VALUES('stu005','c8',47);

INSERT INTO test_z_sc VALUES('stu006','c1',85);

INSERT INTO test_z_sc VALUES('stu006','c2',58);

INSERT INTO test_z_sc VALUES('stu006','c3',47);

INSERT INTO test_z_sc VALUES('stu006','c4',47);

INSERT INTO test_z_sc VALUES('stu006','c5',56);

INSERT INTO test_z_sc VALUES('stu006','c6',25);

INSERT INTO test_z_sc VALUES('stu007','c1',33);

INSERT INTO test_z_sc VALUES('stu007','c2',33);

INSERT INTO test_z_sc VALUES('stu007','c3',96);

INSERT INTO test_z_sc VALUES('stu008','c1',88);

INSERT INTO test_z_sc VALUES('stu008','c2',77);

INSERT INTO test_z_sc VALUES('stu008','c3',55);

INSERT INTO test_z_sc VALUES('stu008','c4',22);

INSERT INTO test_z_sc VALUES('stu009','c1',55);

INSERT INTO test_z_sc VALUES('stu009','c7',55);

INSERT INTO test_z_sc VALUES('stu009','c8',22);

INSERT INTO test_z_sc VALUES('stu010','c1',23);

commit;

-------------------------言归正传，开始吧！ ------------------------

---------(1) 查询课程c1 与 c2 分数相等的学生。

SELECT a.s#

FROM ( SELECT * FROM test_z_sc t1 WHERE t1.c# = 'c1') a

JOIN ( SELECT * FROM test_z_sc t1 WHERE t1.c# = 'c2') b

ON (a.s# = b.s#)

WHERE a.score = b.score

SELECT DISTINCT t.*

FROM test_z_student t

JOIN (

SELECT a.s#

FROM ( SELECT * FROM test_z_sc t1 WHERE t1.c# = 'c1') a

JOIN ( SELECT * FROM test_z_sc t1 WHERE t1.c# = 'c2') b

ON (a.s# = b.s#)

WHERE a.score = b.score

) r

ON (t.s# = r.s#);

---------(2) 查询平均成绩大于60分的同学的学号和平均成绩；

SELECT sc.s#, AVG(sc.score)

FROM test_z_sc sc

GROUP BY sc.s#

HAVING AVG(sc.score) > 60;

SELECT t.*,r.avg_socre FROM test_z_student t

JOIN (

SELECT sc.s#, AVG(sc.score) avg_socre

FROM test_z_sc sc

GROUP BY sc.s#

HAVING AVG(sc.score) > 60

) r ON(r.s# = t.s#)

---------(3) 查询所有同学的学号、姓名、选课数、总成绩；

SELECT sc.s#, COUNT(sc.c#),SUM(sc.score) FROM test_z_sc sc GROUP BY sc.s#;

--方法1

SELECT t.* ,r.c_c,r.s_c FROM test_z_student t

JOIN (

SELECT sc.s#, COUNT(sc.c#) c_c,SUM(sc.score) s_c FROM test_z_sc sc GROUP BY sc.s#

) r ON (t.s# = r.s#) ORDER BY t.s#;

--方法2

思考：LEFT JOIN 与 RIGHT JOIN 究竟用在什么时候，遇到怎样的问题的时候会考虑到。如下：

LEFT JOIN：当需要这边表的所有行数据，同时还需要额外的列额时候可以考虑。同理 RIGHT JOIN一样。

SELECT t.s#,t.sname,COUNT(sc.c#),SUM(sc.score)

FROM test_z_student t LEFT JOIN test_z_sc sc ON (t.s# = sc.s#)

GROUP BY t.s#,t.sname ORDER BY t.s#;

---------(4) 查询姓名以“tcha”开头的老师的个数；

--方法1

SELECT SUM(COUNT(t.tname)) 个数

FROM test_z_teacher t

WHERE t.tname LIKE 'tcha%'

GROUP BY t.tname;

--方法2

SELECT COUNT(DISTINCT(t.tname)) 个数

FROM test_z_teacher t

WHERE t.tname LIKE 'tcha%';

---------(5) 查询没学过“tchd-2-name”老师课的同学的学号、姓名；

--方法1

SELECT stu.*

FROM test_z_student stu

WHERE stu.s# NOT IN

(SELECT sc.s#

FROM test_z_sc sc

WHERE sc.c# IN (SELECT tc.c#

FROM test_z_teacher tec

JOIN test_z_course tc

ON (tec.t# = tc.t#)

WHERE tec.tname = 'tchd-2-name'));

--方法2

SELECT stu.*

FROM test_z_student stu

WHERE stu.s# NOT IN (SELECT sc.s#

FROM test_z_sc sc

JOIN test_z_course tc

ON (sc.c# = tc.c#)

JOIN test_z_teacher tec

ON (tec.t# = tc.t#)

WHERE tec.tname = 'tchd-2-name');

---------(6) 查询学过“c1”并且也学过编号“c6”课程的同学的学号、姓名；

--方法1

SELECT stu.*

FROM test_z_student stu

JOIN (SELECT sc1.s#

FROM test_z_sc sc1

JOIN test_z_sc sc2

ON (sc1.s# = sc2.s#) WHERE　sc1.c# = 'c1'

AND sc2.c# = 'c6') r

ON (stu.s# = r.s#);

--方法2

SELECT stu.* FROM test_z_student stu

JOIN test_z_sc sc ON (stu.s# = sc.s#)

WHERE sc.c# = 'c1'

AND EXISTS (

SELECT * FROM test_z_sc sc2

WHERE sc2.s# = sc.s#

AND sc2.c# = 'c6'

);

---------(7) 查询学过“tchb-1-name”老师所教的所有课的同学的学号、姓名；

思路分析：先查找出学过“tchb-1-name”老师所教的任何一门课程的学生号，这时如果学了多门该老师

的课程的话，同一个学生会有多条记录，这就意味着每一条记录就是学的该老师的一门课程。然后再在

这个结果集中分组，在上一个结果集中的一个学生的重复记录数目等于该老师所教课程的数目就OK。

SELECT stu.*

FROM test_z_student stu

WHERE stu.s# IN

(SELECT sc.s#

FROM test_z_sc sc

JOIN test_z_course tc

ON (sc.c# = tc.c#)

JOIN test_z_teacher tec

ON (tc.t# = tec.t#)

WHERE tec.tname = 'tchb-1-name'

GROUP BY sc.s#

HAVING COUNT(sc.c#) = (SELECT COUNT(tc2.c#)

FROM test_z_course tc2

JOIN test_z_teacher tec2

ON (tc2.t# = tec2.t#)

WHERE tec2.tname = 'tchb-1-name'));

---------(8) 查询课程编号“c2”的成绩比课程编号“c1”课程低的所有同学的学号、姓名；

--方法1

SELECT stu.*

FROM test_z_student stu

WHERE stu.s# IN

(SELECT r_1.s#

FROM (SELECT sc.s#, sc.score FROM test_z_sc sc WHERE sc.c# = 'c2') r_1

JOIN (SELECT sc.s#, sc.score FROM test_z_sc sc WHERE sc.c# = 'c1') r_2

ON (r_1.s# = r_2.s#)

WHERE r_1.score < r_2.score);

--方法2

SELECT stu.*

FROM test_z_student stu

JOIN (SELECT r_1.s#

FROM (SELECT sc.s#, sc.score FROM test_z_sc sc WHERE sc.c# = 'c2') r_1

JOIN (SELECT sc.s#, sc.score FROM test_z_sc sc WHERE sc.c# = 'c1') r_2

ON (r_1.s# = r_2.s#)

WHERE r_1.score < r_2.score) tr

ON (tr.s# = stu.s#);

--方法3

/* 方法3是将向比较的列合并到同一个结果集中。如下：

SELECT stu.s#,

stu.sname,

score,

(SELECT score

FROM test_z_sc sc2

WHERE sc2.s# = stu.s#

AND sc2.c# = 'c2') score2

FROM test_z_student stu, test_z_sc sc

WHERE stu.s# = sc.s#

AND sc.c# = 'c1'

SELECT s#, sname

FROM (SELECT stu.s#,

stu.sname,

score,

(SELECT score

FROM test_z_sc sc2

WHERE sc2.s# = stu.s#

AND sc2.c# = 'c2') score2

FROM test_z_student stu, test_z_sc sc

WHERE stu.s# = sc.s#

AND sc.c# = 'c1') s_2

WHERE score2 < score

---------(9) 查询所有课程成绩小于60分的同学的学号、姓名；

思考：有些问题，得用“反向思考”去解决。如本例，只要有一门课程大于60分的同学就不符合要求，即可排除。

要是不用反向解决问题的办法，否则本例这个问题还很不好解决。

SELECT stu.*

FROM test_z_student stu

WHERE stu.s# NOT IN (SELECT stu2.s#

FROM test_z_student stu2

JOIN test_z_sc sc

ON (stu2.s# = sc.s#)

WHERE sc.score > 60);

---------(10) 查询没有学全所有课的同学的学号、姓名；

思路：中间表按照学生分组后，每个学生分组的COUNT数目小于课程表中的总记录条数即可。

--方法1.采用的 IN 方式

SELECT stu.*

FROM test_z_student stu

WHERE stu.s# IN

(

SELECT sc.s#

FROM test_z_sc sc

GROUP BY sc.s#

HAVING COUNT(sc.c#) < (SELECT COUNT(tc.c#) FROM test_z_course tc));

--方法2. 采用 JOIN 方式。

SELECT stu.*

FROM test_z_student stu

JOIN (SELECT sc.s#

FROM test_z_sc sc

GROUP BY sc.s#

HAVING COUNT(sc.c#) < (SELECT COUNT(tc.c#) FROM test_z_course tc)) r

ON (r.s# = stu.s#);

--方法3

SELECT stu.s#, stu.sname

FROM test_z_student stu

JOIN test_z_sc sc

ON (stu.s# = sc.s#)

GROUP BY stu.s#, stu.sname

HAVING COUNT(sc.c#) < (SELECT COUNT(tc.c#) FROM test_z_course tc)

---------(11) 查询至少有一门课与学号为“stu009”的同学所学相同的同学的学号和姓名；

SELECT DISTINCT stu.*

FROM test_z_student stu

JOIN test_z_sc sc

ON (stu.s# = sc.s#)

WHERE sc.c# IN (SELECT sc2.c# FROM test_z_sc sc2 WHERE sc2.s# = 'stu009');

---------(12) 查询至少学过学号为“stu009”同学所学的任意一门课的其他同学学号和姓名

这句话表达的意思和上一个相同

SELECT DISTINCT stu.*

FROM test_z_student stu

JOIN test_z_sc sc

ON (stu.s# = sc.s#)

WHERE sc.c# IN (SELECT sc2.c# FROM test_z_sc sc2 WHERE sc2.s# = 'stu009');

---------(13) 把“test_z_sc_brk”表中“tchb-1-name”老师教的课的成绩都更改为此课程的平均成绩；

/*未解决*/

---------(14) 查询至少包含“stu002”号的同学所学习的全部课程的同学的学号和姓名；

SELECT stu.s#, stu.sname, COUNT(*)

FROM test_z_student stu

JOIN test_z_sc sc

ON (sc.s# = stu.s#)

WHERE sc.c# IN (SELECT sc2.c# FROM test_z_sc sc2 WHERE　sc2.s# = 'stu002')

GROUP BY stu.s#, stu.sname

HAVING COUNT(*) = (SELECT COUNT(*)

FROM test_z_sc sc3

WHERE sc3.s# = 'stu002');

---------(15) 查询和“stu002”号的同学所学的课程完全相同（不能多也不能少）的同学学号和姓名；

SELECT stu.s#, stu.sname, COUNT(*)

FROM test_z_student stu

JOIN test_z_sc sc

ON (sc.s# = stu.s#)

WHERE sc.c# IN (SELECT sc2.c# FROM test_z_sc sc2 WHERE　sc2.s# = 'stu002')

AND sc.s# NOT IN

(SELECT sc.s#

FROM test_z_sc sc

GROUP BY sc.s#

HAVING COUNT(sc.c#) > (SELECT COUNT(*)

FROM test_z_sc sc3

WHERE sc3.s# = 'stu002'))

GROUP BY stu.s#, stu.sname

HAVING COUNT(*) = (SELECT COUNT(*)

FROM test_z_sc sc3

WHERE sc3.s# = 'stu002');

---------(16) 删除学习“tchb-1-name”老师课的SC表记录；

/*未解决*/

---------(17) 查询如下描述的结果集

按平均成绩从高到低显示所有学生的“c1_name”、“c2_name”、“c3_name”三门的课程成绩，

本例中有效课程数指的是学生学的所有课程，有效平均分是学生学的所有课程的成绩除以该学生所学的课程数。

按如下形式显示：

学生ID,c1_name,c2_name,c1_name,有效课程数,有效平均分

--方法1

SELECT sc.s# 学生ID,

sc1.score c1_name,

sc2.score c2_name,

sc3.score c3_name,

COUNT(*) 有效课程数,

AVG(sc.score) 有效平均分

FROM test_z_sc sc

LEFT JOIN test_z_sc sc1 ON (sc.s# = sc1.s# AND sc1.c# = 'c1')

LEFT JOIN test_z_sc sc2 ON (sc.s# = sc2.s# AND sc2.c# = 'c2')

LEFT JOIN test_z_sc sc3 ON (sc.s# = sc3.s# AND sc3.c# = 'c3')

GROUP BY sc.s#, sc1.score, sc2.score, sc3.score

ORDER BY AVG(sc.score);

--方法2

SELECT sc.s# 学生ID,

NVL((SELECT sc1.score

FROM test_z_sc sc1

WHERE sc.s# = sc1.s#

AND sc1.c# = 'c1'),

0) c1_name,

NVL((SELECT sc2.score

FROM test_z_sc sc2

WHERE sc.s# = sc2.s#

AND sc2.c# = 'c2'),

0) c2_name,

NVL((SELECT sc3.score

FROM test_z_sc sc3

WHERE sc.s# = sc3.s#

AND sc3.c# = 'c3'),

0) c3_name,

COUNT(*) 有效课程数,

AVG(sc.score) 有效平均分

FROM test_z_sc sc

GROUP BY sc.s#

ORDER BY AVG(sc.score);

--上述SQL中方法1的效率明显比方法2的差很多，思考原因在什么地方？

---------(18) 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT sc.c# 课程ID, MAX(sc.score) 最高分, MIN(sc.score) 最低分

FROM test_z_sc sc

GROUP BY sc.c#

ORDER BY sc.c#;

---------(19) 按各科平均成绩从低到高和大于60分的人数。显示格式为：课程ID，平均成绩，大于60分的人数

SELECT sc.c#,

TRUNC(NVL(AVG(sc.score), 0),2) 平均成绩,

SUM(CASE

WHEN NVL(sc.score, 0) >= 60 THEN

ELSE

END) 大于60分的人数

FROM test_z_sc sc

GROUP BY sc.c# ;

---------(20) 查询各科平均成绩和及格（大于60分）率的百分数，并且按照及格率进行升序排序

SELECT sc.c#,

TRUNC(NVL(AVG(sc.score), 0), 2) 平均成绩,

(100 * TRUNC(SUM(CASE

WHEN NVL(sc.score, 0) >= 60 THEN

ELSE

END) / COUNT(*),

4)) || '%' AS 大于60分的比率

FROM test_z_sc sc

GROUP BY sc.c#

ORDER BY (SUM(CASE

WHEN NVL(sc.score, 0) >= 60 THEN

ELSE

END) / COUNT(*));

---------(21) 按要求查询下列结果集

查询如下课程平均成绩和及格率的百分数(用"1行"显示查询结果集)；显示格式如下：

SELECT SUM(CASE

WHEN sc.c# = 'c1' THEN

NVL(sc.score, 0)

ELSE

END) / (SUM(CASE

WHEN sc.c# = 'c1' THEN

ELSE

END)) AS c1_name平均成绩,

TRUNC(NVL(100 * SUM(CASE

WHEN (sc.c# = 'c1' AND NVL(sc.score, 0) >= 60) THEN

ELSE

END) / (SUM(CASE

WHEN sc.c# = 'c1' THEN

ELSE

END)),

0),

4) || '%' AS c1_name及格率,

SUM(CASE

WHEN sc.c# = 'c2' THEN

NVL(sc.score, 0)

ELSE

END) / (SUM(CASE

WHEN sc.c# = 'c2' THEN

ELSE

END)) AS c2_name平均成绩,

TRUNC(NVL(100 * SUM(CASE

WHEN (sc.c# = 'c2' AND NVL(sc.score, 0) >= 60) THEN

ELSE

END) / (SUM(CASE

WHEN sc.c# = 'c2' THEN

ELSE

END)),

0),

4) || '%' AS c2_name及格率,

SUM(CASE

WHEN sc.c# = 'c3' THEN

NVL(sc.score, 0)

ELSE

END) / (SUM(CASE

WHEN sc.c# = 'c3' THEN

ELSE

END)) AS c3_name平均成绩,

TRUNC(NVL(100 * SUM(CASE

WHEN (sc.c# = 'c3' AND NVL(sc.score, 0) >= 60) THEN

ELSE

END) / (SUM(CASE

WHEN sc.c# = 'c3' THEN

ELSE

END)),

0),

4) || '%' AS c3_name及格率

FROM test_z_sc sc;

思考：好好领悟CASE的执行步骤，特别是当它与聚合函数结合起来使用的时候。

---------(22) 查询所有的老师所教的不同课程平均分从高到低显示。显示格式如下：

-- 教师ID，教师姓名，课程ID，课程名称，平均成绩

SELECT MAX(tec.t#) AS 教师ID,

MAX(tec.tname) AS 教师姓名,

sc.c# AS 课程ID,

MAX(tc.cname) AS 课程名称,

NVL(AVG(sc.score), 0) AS 平均成绩

FROM test_z_sc sc

JOIN test_z_course tc ON (sc.c# = tc.c#)

JOIN test_z_teacher tec ON (tc.t# = tec.t#)

GROUP BY sc.c#

ORDER BY NVL(AVG(sc.score), 0);

思考：

上述这个SQL有个技巧，当需要得到不在 GROUP BY 列中的字段时，可以考虑使用聚合函数。如本例的：

MAX(tec.t#) AS 教师ID,

MAX(tec.tname) AS 教师姓名,

MAX(tc.cname) AS 课程名称,

---------(23) 查询如下需求的结果集

查询 c1_name（c1），c2_name（c2），c3_name （c3）课程成绩第 3 名到第 6 名的学生成绩单：

结果集格式如下：

[学生ID],[学生姓名],c1_name成绩,c2_name成绩,UML,c3_name成绩

提示： Oracle查询第M到第N条数据公式：

SELECT *

FROM (SELECT *, ROWNUM AS con

FROM (SELECT * FROM TABLE ORDER BY colum)

WHERE ROWNUM <= N)

WHERE con >= M;

如在本例中查询test_z_sc表的第2条到第4条数据，如下：

SELECT *

FROM (SELECT r.*, ROWNUM AS num

FROM (SELECT * FROM test_z_sc sc) r

WHERE ROWNUM <= 4)

WHERE num >= 2;

--方法1

--第一步：按照要查询的课程排序。没有取学生姓名，关联一下就可以了。

SELECT sc.s# AS 学生ID,

NVL( ( SELECT sc1.score FROM test_z_sc sc1 WHERE sc.s# = sc1.s# AND sc1.c#='c1' ),0) AS c1_name成绩,

NVL( ( SELECT sc2.score FROM test_z_sc sc2 WHERE sc.s# = sc2.s# AND sc2.c#='c2' ),0) AS c2_name成绩,

NVL( ( SELECT sc3.score FROM test_z_sc sc3 WHERE sc.s# = sc3.s# AND sc3.c#='c3' ),0) AS c3_name成绩

FROM test_z_sc sc

GROUP BY sc.s#

ORDER BY c1_name成绩,c2_name成绩,c3_name成绩;

--第二步，在第一步中从排序的结果集中取出第3条到第6条数据

SELECT *

FROM (SELECT t.*, ROWNUM as num

FROM (

SELECT sc.s# AS 学生ID,

NVL( ( SELECT sc1.score FROM test_z_sc sc1 WHERE sc.s# = sc1.s# AND sc1.c#='c1' ),0) AS c1_name成绩,

NVL( ( SELECT sc2.score FROM test_z_sc sc2 WHERE sc.s# = sc2.s# AND sc2.c#='c2' ),0) AS c2_name成绩,

NVL( ( SELECT sc3.score FROM test_z_sc sc3 WHERE sc.s# = sc3.s# AND sc3.c#='c3' ),0) AS c3_name成绩

FROM test_z_sc sc

GROUP BY sc.s#

ORDER BY c1_name成绩,c2_name成绩,c3_name成绩

) t

WHERE ROWNUM <= 6) r

WHERE r.num >= 3;

--方法2.可以使用 LEFT JOIN的方式。

--第一步，查询需要的课程成绩并排序。

SELECT DISTINCT stu.s# AS 学生ID,

stu.sname AS 学生姓名,

NVL(sc1.score,0) AS c1_name成绩,

NVL(sc2.score,0) AS c2_name成绩,

NVL(sc3.score,0) AS c3_name成绩

FROM test_z_sc sc

JOIN test_z_student stu ON( sc.s# = stu.s# )

LEFT JOIN test_z_sc sc1 ON( sc.s# = sc1.s# AND sc1.c# = 'c1' )

LEFT JOIN test_z_sc sc2 ON( sc.s# = sc2.s# AND sc2.c# = 'c2' )

LEFT JOIN test_z_sc sc3 ON( sc.s# = sc3.s# AND sc3.c# = 'c3' )

ORDER BY c1_name成绩,c2_name成绩,c3_name成绩

--第二步，从第一步的结果集中取出第3到第6条数据。这和第一种方法的第二步是一样的。

SELECT *

FROM (SELECT t.*, ROWNUM as num

FROM (

SELECT DISTINCT stu.s# AS 学生ID,

stu.sname AS 学生姓名,

NVL(sc1.score,0) AS c1_name成绩,

NVL(sc2.score,0) AS c2_name成绩,

NVL(sc3.score,0) AS c3_name成绩

FROM test_z_sc sc

JOIN test_z_student stu ON( sc.s# = stu.s# )

LEFT JOIN test_z_sc sc1 ON( sc.s# = sc1.s# AND sc1.c# = 'c1' )

LEFT JOIN test_z_sc sc2 ON( sc.s# = sc2.s# AND sc2.c# = 'c2' )

LEFT JOIN test_z_sc sc3 ON( sc.s# = sc3.s# AND sc3.c# = 'c3' )

ORDER BY c1_name成绩,c2_name成绩,c3_name成绩

) t

WHERE ROWNUM <= 6) r

WHERE r.num >= 3;

---------(24) 统计各科成绩在各分数段人数。结果集显示格式为: 课程ID,课程名称,[90-100],[75-85],[60-70],[ <60]

SELECT sc.c#,tc.cname,

SUM( CASE WHEN NVL(sc.score,0) >=90 AND NVL(sc.score,0) <=100 THEN

ELSE

END ) AS 九十到一百,

SUM( CASE WHEN NVL(sc.score,0) >=75 AND NVL(sc.score,0) <=85 THEN

ELSE

END ) AS 七十五到八十五,

SUM( CASE WHEN NVL(sc.score,0) >=60 AND NVL(sc.score,0) <=70 THEN

ELSE

END ) AS 六十到七十,

SUM( CASE WHEN NVL(sc.score,0) <60 THEN

ELSE

END ) AS 小于60分的人数

FROM test_z_sc sc

JOIN test_z_course tc ON(sc.c# = tc.c# )

GROUP BY sc.c#,tc.cname

---------(25) 查询学生平均成绩及其名次

--方法1。使用ORACLE的位列ROWNUM

SELECT t.*, ROWNUM AS 名次

FROM (SELECT TRUNC(AVG(sc.score), 2) AS 平均成绩

FROM test_z_sc sc

GROUP BY sc.s#

ORDER BY 平均成绩 DESC) t ;

--方法2.直接硬性的排序（排名次）。使用两个一模一样的结果集来排序。不过这种效率很低。但思考方式值得借鉴。

提示：以其中一个结果集为参照，用另一结果集的每一个元素和参照结果集的每个元素做对比。

就是下面查询中的T1结果集与T2结果集。

SELECT 1 +(SELECT COUNT(distinct 平均成绩)

FROM (SELECT sc.s#, TRUNC(AVG(sc.score),2) AS 平均成绩

FROM test_z_sc sc

GROUP BY sc.s#) T1

WHERE 平均成绩 > T2.平均成绩) as 名次,

t2.S# as 学生学号,

t2.平均成绩

FROM (SELECT sc2.s#, TRUNC(AVG(sc2.score),2) 平均成绩

FROM test_z_sc sc2

GROUP BY sc2.s#) T2

ORDER BY 平均成绩 desc;

---------(26) 查询各科成绩前三名的记录（当出现相同分数的时候分别考虑不同的排名方式）；

--显示方式为：课程名称学生ID 分数排名

这里会使用到ORACLE的排名函数。大致介绍如下：

rank,dense_rank,row_number，以及分组排名partition

rank:排名会出现并列第n名，它之后的会跳过空出的名次，例如：1,2,2,4

dense_rank:排名会出现并列第n名，它之后的名次为n+1，例如：1,2,2,3

row_number:排名采用唯一序号连续值，例如1,2,3,4

partition:将排名限制到某一分组

--方法1.row_number() 排名方式

select t1.cname 课程名称, temp.s# 学生ID, temp.score 分数,temp.排名

from (select t.c#,

t.s#,

row_number() over(partition by t.c# order by t.score desc) 排名,

t.score

from test_z_sc t) temp,

test_z_course t1

where temp.c# = t1.c#

and temp.排名 < 4;

--方法2.RANK() 排名方式

select t1.cname 课程名称, temp.s# 学生ID, temp.score 分数,temp.排名

from (select t.c#,

t.s#,

RANK() over(partition by t.c# order by t.score desc) 排名,

t.score

from test_z_sc t) temp,

test_z_course t1

where temp.c# = t1.c#

and temp.排名 < 4 ;

--方法3.dense_rank() 排名方式

select t1.cname 课程名称, temp.s# 学生ID, temp.score 分数,temp.排名

from (select t.c#,

t.s#,

dense_rank() over(partition by t.c# order by t.score desc) 排名,

t.score

from test_z_sc t) temp,

test_z_course t1

where temp.c# = t1.c#

and temp.排名 < 4 ;

---------(27) 以总分排名查询所有同学的名次（当出现相同总数的时候分别考虑不同的排名方式）:

--显示方式为：学生ID 总分排名

--当出现总分相同的时候可以参考上面的实现方式

--方法1.用ORACLE的排名函数

SELECT r.s# 学生ID ,

r.total 总分 ,

RANK() OVER( ORDER BY r.total DESC ) 排名

FROM (

SELECT sc.s#,SUM(sc.score) total

FROM test_z_sc sc

GROUP BY sc.s#

) r ;

--方法2.用ROWNUM的方式。但这种方式不能考虑多种并列排序方式。

SELECT r.*, ROWNUM 排名

FROM (SELECT sc.s# 学生ID, SUM(sc.score) 总分

FROM test_z_sc sc

GROUP BY sc.s#

ORDER BY 总分 DESC) r

---------(28) 查询每门课程被选修的学生数

SELECT sc.c# 课程ID , COUNT(sc.s#) 学生数

FROM test_z_sc sc

GROUP BY sc.c# ;

---------(29) 查询出只学了2门课程的全部学生的学号和姓名

SELECT sc.s#,

stu.sname

FROM test_z_sc sc

JOIN test_z_student stu ON(stu.s# = sc.s#)

GROUP BY sc.s#,stu.sname

HAVING COUNT(sc.s#) = 2 ;

---------(30) 查询男生、女生人数。显示格式为：性别，人数

--方法1。CASE用法

SELECT CASE

WHEN UPPER(stu.ssex) = 'M' THEN

'男'

WHEN UPPER(stu.ssex) = 'F' THEN

'女'

ELSE

'未知'

END 性别,

COUNT(stu.ssex) 人数

FROM test_z_student stu

GROUP BY stu.ssex;

--方法2。DECODE函数用法

DECODE(UPPER(stu.ssex),'M','男','F','女','未知') 解释：

选定标准的值为UPPER(stu.ssex)。当UPPER(stu.ssex)为“M”的时候返回“男”，

当UPPER(stu.ssex)为“F”的时候返回“女”，否则为“未知”。以此类推。

SELECT DECODE(UPPER(stu.ssex),'M','男','F','女','未知') 性别,

COUNT(stu.ssex) 人数

FROM test_z_student stu

GROUP BY stu.ssex;

---------(31) 查询姓名以“stu001”开头的的学生名单

SELECT stu.*

FROM test_z_student stu

WHERE stu.sname LIKE 'stu001%';

---------(32) 查询姓名的前三个字符为任意字符，且第四、第五个字符以此为“ss”，其余的字符为任意字符的这类同学名单。

考察两中通配符： % 与 _

解释如下：

% 匹配任意多个任意字符

_ 匹配单个任意字符

SELECT stu.*

FROM test_z_student stu

WHERE stu.sname LIKE '___ss%' ;

---------(33) 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

SELECT sc.c# 课程ID,

TRUNC(AVG(sc.score),2) 课程平均分

FROM test_z_sc sc

GROUP BY sc.c#

ORDER BY AVG(sc.score) ASC, sc.c# DESC ;

---------(34) 查询平均成绩大于等于60的所有学生的学号、姓名和平均成绩，并按平均分从高到低排序。

SELECT sc.s# 学号,

stu.sname 学生姓名,

TRUNC(AVG(sc.score),3) 平均分

FROM test_z_sc sc

JOIN test_z_student stu ON(sc.s# = stu.s#)

GROUP BY sc.s#,stu.sname

HAVING AVG(sc.score) >= 60

ORDER BY TRUNC(AVG(sc.score),3) DESC ;

---------(35) 查询课程名称为“c1-name”，且分数低于60的学生姓名和分数

SELECT sc.s# 学生号,

stu.sname 学生姓名,

sc.score 分数

FROM test_z_sc sc

JOIN test_z_course tc ON(sc.c# = tc.c#)

JOIN test_z_student stu ON(stu.s# = sc.s#)

WHERE tc.cname = 'c1-name'

AND sc.score < 60 ;

---------(36) 查询所有学生的选课情况；显示格式为：学生号，学生姓名，课程ID，课程名称

SELECT stu.s# 学生号,

stu.sname 学生姓名,

sc.c# 课程ID,

tc.cname 课程名称

FROM test_z_sc sc

JOIN test_z_student stu ON(sc.s# = stu.s#)

JOIN test_z_course tc ON(sc.c# = tc.c#) ;

---------(37) 查询任何一门课程成绩在90分以上的,学生号，学生姓名、课程名称和分数；

SELECT sc.s# 学生号,

stu.sname 学生姓名,

tc.cname 课程名称,

sc.score 分数

FROM test_z_sc sc

JOIN test_z_course tc ON(sc.c# = tc.c#)

JOIN test_z_student stu ON(stu.s# = sc.s#)

WHERE sc.score > 90

---------(38) 查询选修“tchb-1-name”老师所授课程的学生中，成绩最高的学生姓名及其成绩

SELECT sc.s# 学生号,

MAX(sc.score) 分数

FROM test_z_sc sc

JOIN test_z_course tc ON(sc.c# = tc.c#)

JOIN test_z_teacher tec ON(tc.t# = tec.t# AND tec.tname = 'tchb-1-name')

GROUP BY sc.s#

---------(39) 查询各个课程及相应的选修人数

SELECT sc.c# 课程ID,

tc.cname 课程名称,

COUNT(sc.s#) 选修人数

FROM test_z_sc sc

JOIN test_z_course tc ON(sc.c# = tc.c#)

GROUP BY sc.c# , tc.cname

ORDER BY 选修人数 DESC, sc.c# ASC ;

---------(40) 查询不同课程成绩相同的学生的学号、课程号、学生成绩

SELECT sc1.s#,sc1.c#,sc1.score FROM test_z_sc sc1

JOIN test_z_sc sc2 ON(sc1.score = sc2.score)

WHERE sc1.c# != sc2.c#

AND sc1.s# = sc2.s# ;

--或者

SELECT sc2.s#,sc2.c#,sc2.score FROM test_z_sc sc1

JOIN test_z_sc sc2 ON(sc1.score = sc2.score)

WHERE sc1.c# != sc2.c#

AND sc1.s# = sc2.s# ;

---------(41) 查询每门功成绩最好的前两名 ,请参考(26)

/* 在此给除你的答案。 */

---------(42) 按下列要求查询结果集。参考(39)；

统计每门课程的学生选修人数（超过6人的课程才统计）。

要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT sc.c# 课程ID,

tc.cname 课程名称,

COUNT(sc.s#) 选修人数

FROM test_z_sc sc

JOIN test_z_course tc ON(sc.c# = tc.c#)

GROUP BY sc.c# , tc.cname

HAVING COUNT(sc.s#) > 6

ORDER BY 选修人数 DESC, sc.c# ASC ;

---------(43) 检索至少选修4门课程的学生学号。

SELECT sc.s# 学生号,

COUNT(sc.c#) 选修的课程数

FROM test_z_sc sc

GROUP BY sc.s#

HAVING COUNT(sc.c#) >3 ;

---------(44) 查询全部学生都选修的课程的课程号和课程名

SELECT sc.c# 全部学生选的课程ID,

tc.cname 全部学生选的课程名称

FROM test_z_sc sc

JOIN test_z_course tc ON(tc.c# = sc.c#)

GROUP BY sc.c#,tc.cname

HAVING COUNT(sc.s#) = (SELECT COUNT(*) FROM test_z_student ) ;

---------(45) 查询被学生们选修的课程有哪些（也就是说只要被任何学生选修就可以，不一定需要所有学生选修同一门课程）？

select C#,Cname

from Course

where C# in (select c# from sc group by c#) ;

---------(46) 查询同时被全部学生选修的那些课程（比如：语文必须被全部学生选修才算，只要有任何一个没有选修就不算）

SELECT tc.c#,

tc.cname

FROM test_z_course tc

WHERE tc.c# IN(SELECT sc.c# FROM test_z_sc sc GROUP BY sc.c# )

---------(47) 查询没学过“tchb-1-name”老师讲授的任一门课程的学生姓名；

思考：如前面所述，有些问题用正面的方法很难解决，这时不妨用逆向思维。就像以前学数学里面的

证明题一样，要求得A、B两个集合的交集，虽然我求不出来A、B的交集，但能求得A、B的非交集，则

剩下的就是交集了。这里简单总结一下，什么时候用到这种思维方式最多。如下：

当涉及到中间表的过滤，常见于“N”对“N”的关系表，用 “N”的一端作为条件来过滤关系表，且常见的

条件词语有“至少”、“全部” 等等。例如本例中，学生表和课程表是多对多的关系，要查询没有学过某个

老师的任何一门课课程的学生，这里注意，如果某个老师教了c1与c2两门课程，学生1选择了其中的c1，

即使没有选择c2也不能被排除在外，除非c1与c2两门课程都没有选。如果直接求交接很难搞，这时我们

换一中思考方式：查询出只要学过某个老师所教的课程的那些学生的结果集R，然后再查询学生表只要

不在结果集R中的学生，都是没有学过该老师的任何一门课程的学生。

SELECT stu.s# 学生号,

stu.sname 学生姓名

FROM test_z_student stu

WHERE stu.s# NOT IN (

SELECT sc.s# FROM test_z_sc sc

JOIN test_z_course tc ON(sc.c# = tc.c#)

JOIN test_z_teacher tec ON(tec.t# = tc.t# AND tec.tname = 'tchb-1-name')

) ;

---------(48) 查询至少有5门课程不及格课程的同学的学号及其平均成绩

SELECT sc.s# 学生号,

AVG(sc.score) 平均分

FROM test_z_sc sc

WHERE sc.score < 60

GROUP BY sc.s#

HAVING COUNT(sc.s#) >= 5

---------(49) 查询课程编号为“c1”的课程分数小于70，按分数降序排列的学生学号

SELECT sc.s# 学生号,

sc.c# 课程号,

sc.score 分数

FROM test_z_sc sc

WHERE sc.c# = 'c1'

AND sc.score <70

ORDER BY sc.score DESC ;

-------------------- 完 -------------------

-- 如果你对ORACLE执行计划感兴趣，可以去了解一下如下的1、2、3 。另外总结一下下面的4、5两点。

1、 ORACLE plan 的 Filter

2、 ORACLE plan 的 Filter与HASHJOIN比较

3、ORACLE PLAN 什么时候产生VIEW？

4、JOIN 一个子查询的时候，子查询内不能使用外层表的字段作为条件。如下的AND sc1.c# = sc.c#：

SELECT sc.c#, AVG(sc.score), r_1.gt60

FROM test_z_sc sc

JOIN (SELECT sc1.c#, COUNT(sc1.c#) gt60

FROM test_z_sc sc1

WHERE sc1.score >= 60

AND sc1.c# = sc.c# --这里不能使用sc

GROUP BY sc1.c#) r_1 ON (r_1.c# = sc.c#)

GROUP BY sc.c#, r_1.gt60

ORDER BY AVG(sc.score)

5、总结EXISTS的用法，且区分于NOT IN或者 IN 的区别。

Isa1_2_3

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL语句专题

/*概述：前段时，不就间看了一个牛人的博客，来检查一下SQL的能力的文章，刚开始觉得没什么几张简单表吗？其实最后看了看就从这么4张简单表中竟然能挖掘出那么多的SQL语句。而且有些SQL实话说还真有点不好搞。那篇博客是基于SQL SERVER的，我从新来用ORACLE实现一次，且我会尽量想多种实现方式去写，需要的的话，可以比较他们之间的性能差异。
复制链接

扫一扫

专栏目录