sql找到每门课程都超过80分的学生

今天遇到一个题，笔试，sql找到每门课程都超过80分的学生。

当时做错了，哎，基本上没有接触过sql，不是很熟啊。

回来重新试了下，成功了。

 1 mysql> select * from student;
 2 +------------+--------+-------+
 3 | name       | item   | score |
 4 +------------+--------+-------+
 5 | zhangsan   | yuwen  |    81 |
 6 | zhangsan   | shuxue |    85 |
 7 | zhangsan   | yingyu |    75 |
 8 | lisi       | yuwen  |    81 |
 9 | lisi       | shuxue |    81 |
10 | lisi       | yingyu |    81 |
11 | wangermazi | yingyu |    82 |
12 | wangermazi | shuxue |    69 |
13 | wangermazi | yuwen  |    19 |
14 +------------+--------+-------+
15 9 rows in set (0.00 sec)
16 
17 mysql> select name,count(*) as nice from student where score>80 group by name having count(*)>=3;
18 +------+------+
19 | name | nice |
20 +------+------+
21 | lisi |    3 |
22 +------+------+
23 1 row in set (0.00 sec)
24 
25 mysql> select name from student where score>80 group by name having count(*)>=3;
26 +------+
27 | name |
28 +------+
29 | lisi |
30 +------+
31 1 row in set (0.00 sec)

我忘了加“having count(*)>=3”了。

posted @ 2013-08-06 22:37  快活王怜花  阅读(232)  评论(0)    收藏  举报