关闭

[置顶] mysql七表查询实例(二)

标签: mysql嵌套查询求平均值导致的数值类型转变奇葩七表
520人阅读 评论(0) 收藏 举报
分类:

上一章的内容大家觉得怎么样?

接下来,让我们来一点一点继续深入。

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)
通过这个案例我们可以清楚的看到七个表的联合查询还是很有挑战性的,很多时候查询出来的结果虽然是对的,甚至有些时候就连语句看起来也是对的......但是,呃,其实都是错的。真正真确的语句是必须经得起反复验证的(终于搞定了,鼓励下)。

















1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:29087次
    • 积分:1258
    • 等级:
    • 排名:千里之外
    • 原创:93篇
    • 转载:13篇
    • 译文:1篇
    • 评论:5条
    博客专栏
    最新评论
    个人主页
    百度百科个人主页:移动终端网址:www.baidu.com/p/number1killer PC端网址:www.baidu.com/p/number1killer 登录个人简历上的个人主页,点击“他在百科”可查看我的词条贡献等情况。 CSDN个人主页:移动终端网址:http://m.blog.csdn.net/number1kille PC端网址:http://blog.csdn.net/number1killer
    相关资料和知识点
    与本文相关的资料以及和文章中的知识相关的知识点