mysql中實作七表查詢實例（二）

上一章的內容大家覺得怎麼樣？

接下來，讓我們來一點一點繼續深入。

1.查詢成績大於60分的所有資訊：

mysql> select * from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and
 tadd.t=teacher.t) and cphone.s=student.s)as a1 where score>60;
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+
|    1 | 刘一   | 福建   |   18 |     2 |    2 | 数学   |    78 | 贺高   | 深圳   | 男   |  12345 |
|    1 | 刘一   | 福建   |   18 |     3 |    3 | 英语   |    67 | 杨艳   | 上海   | 男   |  12345 |
|    2 | 钱二   | 深圳   |   19 |     1 |    1 | 语文   |    79 | 叶平   | 福建   | 女   |  12346 |
|    2 | 钱二   | 深圳   |   19 |     2 |    2 | 数学   |    81 | 贺高   | 深圳   | 女   |  12346 |
|    2 | 钱二   | 深圳   |   19 |     3 |    3 | 英语   |    92 | 杨艳   | 上海   | 女   |  12346 |
|    2 | 钱二   | 深圳   |   19 |     4 |    4 | 物理   |    68 | 周磊   | 湖南   | 女   |  12346 |
|    3 | 张三   | 上海   |   17 |     1 |    1 | 语文   |    91 | 叶平   | 福建   | 男   |  12347 |
|    3 | 张三   | 上海   |   17 |     3 |    3 | 英语   |    88 | 杨艳   | 上海   | 男   |  12347 |
|    4 | 李四   | 湖南   |   18 |     2 |    2 | 数学   |    88 | 贺高   | 深圳   | 女   |  12348 |
|    4 | 李四   | 湖南   |   18 |     3 |    3 | 英语   |    90 | 杨艳   | 上海   | 女   |  12348 |
|    4 | 李四   | 湖南   |   18 |     4 |    4 | 物理   |    93 | 周磊   | 湖南   | 女   |  12348 |
|    5 | 王五   | 江西   |   17 |     3 |    3 | 英语   |    78 | 杨艳   | 上海   | 男   |  12349 |
|    6 | 赵六   | 广西   |   19 |     2 |    2 | 数学   |    68 | 贺高   | 深圳   | 女   |  13349 |
|    6 | 赵六   | 广西   |   19 |     4 |    4 | 物理   |    71 | 周磊   | 湖南   | 女   |  13349 |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+
14 rows in set (0.00 sec)

2.查詢年齡為18歲的男性學生考試成績大於60分的所有資訊：

mysql> select * from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and
 tadd.t=teacher.t) and cphone.s=student.s)as a1 where (score>60 and sage=18)and ssex="男";
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+
|    1 | 刘一   | 福建   |   18 |     2 |    2 | 数学   |    78 | 贺高   | 深圳   | 男   |  12345 |
|    1 | 刘一   | 福建   |   18 |     3 |    3 | 英语   |    67 | 杨艳   | 上海   | 男   |  12345 |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+
2 rows in set (0.00 sec)

3.查詢每個學生參加課程的總數，總分，平均分數及其所有資訊（CNAME字段除外）：

#看起來很簡單，但是：

mysql> select count(t),sum(score),avg(score),student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone from(select student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone
 where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1;
ERROR 1054 (42S22): Unknown column 'student.s' in 'field list'

既然AS新表了，就不要再把查詢的欄位歸到舊表下面了。

mysql> select count(t),sum(score),avg(score),s,sname,cadd,sage,class,score,t,tname,tadd,ssex,cphone from(select student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone from student,teacher,sc,course,
 cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1;
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
| count(t) | sum(score) | avg(score) | s    | sname  | cadd   | sage | class | score | t    | tname  | tadd   | ssex | cphone |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
|       21 |       1483 |    70.6190 |    1 | 刘一   | 福建   |   18 |     1 |    56 |    1 | 叶平   | 福建   | 男   |  12345 |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
1 row in set (0.08 sec)

#沒有指定分組，就直接把第一行的數據套過來了。

正解：

mysql> select a1.*,sum(score),count(t),avg(score) from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and
 teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s;
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------------+----------+------------+
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone | sum(score) | count(t) | avg(score) |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------------+----------+------------+
|    1 | 刘一   | 福建   |   18 |     1 |    1 | 语文   |    56 | 叶平   | 福建   | 男   |  12345 |        259 |        4 |    64.7500 |
|    2 | 钱二   | 深圳   |   19 |     1 |    1 | 语文   |    79 | 叶平   | 福建   | 女   |  12346 |        320 |        4 |    80.0000 |
|    3 | 张三   | 上海   |   17 |     1 |    1 | 语文   |    91 | 叶平   | 福建   | 男   |  12347 |        282 |        4 |    70.5000 |
|    4 | 李四   | 湖南   |   18 |     2 |    2 | 数学   |    88 | 贺高   | 深圳   | 女   |  12348 |        271 |        3 |    90.3333 |
|    5 | 王五   | 江西   |   17 |     1 |    1 | 语文   |    46 | 叶平   | 福建   | 男   |  12349 |        177 |        3 |    59.0000 |
|    6 | 赵六   | 广西   |   19 |     1 |    1 | 语文   |    35 | 叶平   | 福建   | 女   |  13349 |        174 |        3 |    58.0000 |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------------+----------+------------+
6 rows in set (0.00 sec)

4.查詢每個學生參加課程的總數，總分，平均分數及其所有資訊後按照總分排降序後顯示評價分不小於80分的學生的所有資訊：

mysql>  select * from(select count(t),sum(score),avg(score),s,sname,cadd,sage,class,score,t,tname,tadd,ssex,cphone from(select student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone from student,teacher,sc,course,
 cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s order by sum(score) desc)a3 having avg(score)>=80;
Empty set (0.00 sec)

#SQL語句是沒有錯誤的（CNAME欄位漏了），但是結果就......

+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
| count(t) | sum(score) | avg(score) | s    | sname  | cadd   | sage | class | score | t    | tname  | tadd   | ssex | cphone |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
|        4 |        320 |    80.0000 |    2 | 钱二   | 深圳   |   19 |     1 |    79 |    1 | 叶平   | 福建   | 女   |  12346 |
|        4 |        282 |    70.5000 |    3 | 张三   | 上海   |   17 |     1 |    91 |    1 | 叶平   | 福建   | 男   |  12347 |
|        3 |        271 |    90.3333 |    4 | 李四   | 湖南   |   18 |     2 |    88 |    2 | 贺高   | 深圳   | 女   |  12348 |
|        4 |        259 |    64.7500 |    1 | 刘一   | 福建   |   18 |     1 |    56 |    1 | 叶平   | 福建   | 男   |  12345 |
|        3 |        177 |    59.0000 |    5 | 王五   | 江西   |   17 |     1 |    46 |    1 | 叶平   | 福建   | 男   |  12349 |
|        3 |        174 |    58.0000 |    6 | 赵六   | 广西   |   19 |     1 |    35 |    1 | 叶平   | 福建   | 女   |  13349 |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
6 rows in set (0.00 sec)

mysql>  select * from(select count(t),sum(score),avg(score),s,sname,cadd,sage,class,score,t,tname,tadd,ssex,cphone from(select student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone from student,teacher,sc,course,
 cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s order by sum(score) desc)a3 having avg(score)>0;
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
| count(t) | sum(score) | avg(score) | s    | sname  | cadd   | sage | class | score | t    | tname  | tadd   | ssex | cphone |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
|        4 |        320 |    80.0000 |    2 | 钱二   | 深圳   |   19 |     1 |    79 |    1 | 叶平   | 福建   | 女   |  12346 |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
1 row in set (0.00 sec)

#而而這個語句，這個結果的出現令我徹底蒙圈了。

最後想了一個以毒攻毒的辦法終於將之拿下。

mysql>  select * from(select count(t),sum(score),avg(score),s,sname,cadd,sage,class,score,t,tname,tadd,ssex,cphone from(select student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone from student,teacher,sc,course,
 cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s order by sum(score) desc)a3 having sum(score)/4>=80;
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
| count(t) | sum(score) | avg(score) | s    | sname  | cadd   | sage | class | score | t    | tname  | tadd   | ssex | cphone |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
|        4 |        320 |    80.0000 |    2 | 钱二   | 深圳   |   19 |     1 |    79 |    1 | 叶平   | 福建   | 女   |  12346 |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
1 row in set (0.02 sec)

#然而並沒有什麼卵用. .....

當然還是有正解的：

#

mysql> select * from (select a1.*,sum(score) as ss,count(t) as ct,avg(score) as a3 from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone
 where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s desc)as a2 where a3=80 or a3>80;
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone | ss   | ct | a3      |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
|    4 | 李四   | 湖南   |   18 |     2 |    2 | 数学   |    88 | 贺高   | 深圳   | 女   |  12348 |  271 |  3 | 90.3333 |
|    2 | 钱二   | 深圳   |   19 |     1 |    1 | 语文   |    79 | 叶平   | 福建   | 女   |  12346 |  320 |  4 | 80.0000 |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
2 rows in set (0.00 sec)

出於科學嚴謹的態度（呃，好吧，是偶搞錯太多次了，怕了），讓我們一起來驗證一下：

mysql> select * from (select a1.*,sum(score) as ss,count(t) as ct,avg(score) as a3 from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and
 teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s desc)as a2 where a3=80 or a3>60;
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone | ss   | ct | a3      |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
|    4 | 李四   | 湖南   |   18 |     2 |    2 | 数学   |    88 | 贺高   | 深圳   | 女   |  12348 |  271 |  3 | 90.3333 |
|    3 | 张三   | 上海   |   17 |     1 |    1 | 语文   |    91 | 叶平   | 福建   | 男   |  12347 |  282 |  4 | 70.5000 |
|    2 | 钱二   | 深圳   |   19 |     1 |    1 | 语文   |    79 | 叶平   | 福建   | 女   |  12346 |  320 |  4 | 80.0000 |
|    1 | 刘一   | 福建   |   18 |     1 |    1 | 语文   |    56 | 叶平   | 福建   | 男   |  12345 |  259 |  4 | 64.7500 |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
4 rows in set (0.00 sec)

mysql> select * from (select a1.*,sum(score) as ss,count(t) as ct,avg(score) as a3 from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and
 teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s desc)as a2;
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone | ss   | ct | a3      |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
|    6 | 赵六   | 广西   |   19 |     1 |    1 | 语文   |    35 | 叶平   | 福建   | 女   |  13349 |  174 |  3 | 58.0000 |
|    5 | 王五   | 江西   |   17 |     1 |    1 | 语文   |    46 | 叶平   | 福建   | 男   |  12349 |  177 |  3 | 59.0000 |
|    4 | 李四   | 湖南   |   18 |     2 |    2 | 数学   |    88 | 贺高   | 深圳   | 女   |  12348 |  271 |  3 | 90.3333 |
|    3 | 张三   | 上海   |   17 |     1 |    1 | 语文   |    91 | 叶平   | 福建   | 男   |  12347 |  282 |  4 | 70.5000 |
|    2 | 钱二   | 深圳   |   19 |     1 |    1 | 语文   |    79 | 叶平   | 福建   | 女   |  12346 |  320 |  4 | 80.0000 |
|    1 | 刘一   | 福建   |   18 |     1 |    1 | 语文   |    56 | 叶平   | 福建   | 男   |  12345 |  259 |  4 | 64.7500 |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
6 rows in set (0.00 sec)

透過這個案例我們可以清楚的看到七個表的聯合查詢還是很有挑戰性的，很多時候查詢出來的結果雖然是對的，甚至有些時候就連語句看起來也是對的......但是，呃，其實都是錯的。真正真確的語句是必須經得起反覆驗證的（終於搞定了，鼓勵）。

以上是mysql中實作七表查詢實例（二）的詳細內容。更多資訊請關注PHP中文網其他相關文章！