MYSQL超难多表查询

Question

xstudent(学生表)(学生ID)  (名字) (性别)  (生日)     (班级)   
xsid       xsnm  xssex  xsbirthday  sclass108        曾华  男     09/01/77     95033
105        匡明  男     10/02/75     95031
107        王丽  女     01/23/76     95033
101        李军  男     02/20/76     95033
109        王芳  女     02/10/75     95031
103        陆军  男     06/03/74     95031score(组合表)(学生ID) (课程ID)  (成绩)
xsid      kuid     degree103       3-245     84
105       3-245     67
109       3-245     98
103       3-105     78
105       3-105     91
109       3-105     68
101       3-105     93
107       3-105     72
108       3-105     66
101       6-166     80
107       6-166     77
108       6-166     62course(课程表)(课程ID)    (课名字)    (老师ID)
kuid        kmname      lsid3-105       计算机导论  825
3-245       操作系统    804
6-166       数字电路    856
9-888       高等数学    831teacher(老师表)(老师ID)   (名字)  (性别)   (生日)     (职称)   (系)
lsid       lsname  lssex   lsbirthday  lsprof  lsdepart 804         李城   男      12/02/58    副教授  计算机系
856         张旭   男      03/12/69    讲师    电子工程系
825         王萍   女      05/05/72    助教    计算机系
831         刘冰   女      08/14/77    助教    电子工程系
①．查询“计算机系”与“电子工程系”不同职称的教师的lsname和lsprof
②．查询选修课编号是“3-105”课程且成绩至少高于选修课程编号为“3-245”的同学的kuid,xsid和degree，并且degree从 高到低次序排列
③．在屏幕上列出选修编号为“3-105”并且成绩高于选修课程编号为“3-245”课程的同学的kuid,xsid,degree④．查询成绩比该课程平均成绩低的同学的成绩表     ⑤．查询平均成绩最低的学生姓名及其平均成绩⑥．查询选修多门课程的同学中分数为非最高成绩的记录

Answer 1

mysql> select * from xstudent;
+------+------+-------+------------+--------+
| xsid | xsnm | xssex | xsbirthday | sclass |
+------+------+-------+------------+--------+
|  101 | 李军 | 男    | 1976-02-20 |  95033 |
|  103 | 陆军 | 男    | 1974-06-03 |  95031 |
|  105 | 匡明 | 男    | 1975-10-02 |  95031 |
|  107 | 王丽 | 女    | 1976-01-23 |  95033 |
|  108 | 曾华 | 男    | 1977-09-01 |  95033 |
|  109 | 王芳 | 女    | 1975-02-10 |  95031 |
+------+------+-------+------------+--------+
6 rows in set (0.00 sec)mysql> select * from score;
+------+-------+--------+
| xsid | kuid  | degree |
+------+-------+--------+
|  103 | 3-245 |     84 |
|  105 | 3-245 |     67 |
|  109 | 3-245 |     98 |
|  103 | 3-105 |     78 |
|  105 | 3-105 |     91 |
|  109 | 3-105 |     68 |
|  101 | 3-105 |     93 |
|  107 | 3-105 |     72 |
|  108 | 3-105 |     66 |
|  101 | 6-166 |     80 |
|  107 | 6-166 |     77 |
|  108 | 6-166 |     62 |
+------+-------+--------+
12 rows in set (0.00 sec)mysql> select * from course;
+-------+------------+------+
| kuid  | kmname     | lsid |
+-------+------------+------+
| 3-105 | 计算机导论 |  825 |
| 3-245 | 操作系统   |  804 |
| 6-166 | 数字电路   |  856 |
| 9-888 | 高等数学   |  831 |
+-------+------------+------+
4 rows in set (0.00 sec)mysql> select * from teacher;
+------+--------+-------+------------+--------+------------+
| lsid | lsname | lssex | lsbirthday | lsprof | lsdepart   |
+------+--------+-------+------------+--------+------------+
|  804 | 李城   | 男    | 1958-12-02 | 副教授 | 计算机系   |
|  825 | 王萍   | 女    | 1972-05-05 | 助教   | 计算机系   |
|  831 | 刘冰   | 女    | 1977-08-14 | 助教   | 电子工程系 |
|  856 | 张旭   | 男    | 1969-03-12 | 讲师   | 电子工程系 |
+------+--------+-------+------------+--------+------------+
4 rows in set (0.00 sec)mysql>

Answer 2

①．查询“计算机系”与“电子工程系”不同职称的教师的lsname和lsprof
mysql> select lsname,lsprof
    -> from teacher t
    -> where lsdepart='计算机系'
    -> and not exists (select 1 from teacher where lsdepart='电子工程系' and lsprof=t.lsprof)
    -> union all
    -> select lsname,lsprof
    -> from teacher t
    -> where lsdepart='电子工程系'
    -> and not exists (select 1 from teacher where lsdepart='计算机系' and lsprof=t.lsprof);
+--------+--------+
| lsname | lsprof |
+--------+--------+
| 李城   | 副教授 |
| 张旭   | 讲师   |
+--------+--------+
2 rows in set (0.00 sec)mysql>或者mysql> select lsid,lsname,lsprof
    -> from teacher t
    -> where (lsdepart='计算机系' or lsdepart='电子工程系')
    -> and not exists (select 1 from teacher
    ->  where lsdepart!=t.lsdepart
    ->  and lsprof=t.lsprof
    ->  and (lsdepart='计算机系' or lsdepart='电子工程系')
    ->  );
+------+--------+--------+
| lsid | lsname | lsprof |
+------+--------+--------+
|  804 | 李城   | 副教授 |
|  856 | 张旭   | 讲师   |
+------+--------+--------+
2 rows in set (0.00 sec)mysql>
mysql>[/code]

Answer 3

本帖最后由 ACMAIN_CHM 于 2009-06-14 09:14:16 编辑

  

Answer 4

非常感谢你的回复 请教你一下
②．③．题 为什么91>98那??
不是“3-105”课程的成绩一一对比“3-245”课程的成绩吗??编号为“3-105”并且成绩高于“3-245”课程
“3-105”课程 >“3-245”课程 的同学
91 > 98(“3-245”课程最大成绩)