Example of implementing seven-table query in mysql (2)

What do you think of the content of the previous chapter?

Next, let’s continue to delve deeper bit by bit.

1. Query all information about scores greater than 60 points:

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. Query all information about male students aged 18 years old whose test scores are greater than 60 points:

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. Query the total number of courses attended by each student, total score, average score and all its information (except the CNAME field):

It seems simple, but:

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'

Now that AS has a new table, don’t put the queried fields under the old table.

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)

#If no grouping is specified, just put the first line The data has been transferred.

Correct answer:

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. Query the total number of courses attended by each student, the total score, the average score and all the information, then sort the total score in descending order and display all the information of the students whose evaluation score is not less than 80 points:

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)

##The SQL statement is correct (the CNAME field is missing ), but the result is...

+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
| 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)

And this statement, the appearance of this result completely confused me.

Finally, I thought of a way to fight fire with fire and finally captured it.

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)

## However, it was of no use. .....

Of course there is a correct answer:

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)

Out of a scientifically rigorous attitude (well, okay, I’ve made mistakes too many times, I’m scared), let’s verify it together. ：

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)

Through this case, we can clearly see that the joint query of seven tables is still very challenging. In many cases, the results of the query are correct, and sometimes even Even the sentences look right... but, well, they're all wrong. A truly true statement must withstand repeated verification (I finally got it, please encourage me).

The above is the detailed content of Example of implementing seven-table query in mysql (2). For more information, please follow other related articles on the PHP Chinese website!