Query all subject information of students whose scores in each subject are greater than 80

From Putrid Iguana, 1 Year ago, written in SQL, viewed 236 times.

TABLE_NAME：score
id  name    object  score
1   小强  语文  85
2   小强  数学  73
7   小强  美术  90
5   小狗  语文  80
6   小狗  数学  92
9   小狗  美术  95
3   小花  语文  70
4   小花  数学  79
8   小花  美术  85
 
 
SELECT * FROM score a WHERE a.score >= 80 AND NOT EXISTS(SELECT 1 FROM score b WHERE b.`name`=a.`name` AND b.object != a.object AND b.score < 80) ORDER BY a.`name`;
该语句的思路是：查出当前用户的信息，只要不存在：同用户、不同科目、分数小于80
 
SELECT * FROM score a WHERE a.`name` NOT IN (SELECT DISTINCT(b.`name`) FROM score b WHERE b.score < 80)
该语句的思路是：查询每科成绩都大于80分的学生的所有科目信息，也就是说只要有一科分数小于80分的就不满足条件
 
相对两个语句来说，第二句思路好理解一点。应该还有其他很多种写法实现这种查询，欢迎指点！
 

Reply to "Query all subject information of students whose scores in each subject are greater than 80"

Here you can reply to the paste above

Author What's your name?

Title Give your paste a title.

Language What language is your paste written in?

Your paste Paste your paste here

Create Shorturl Create a shorter url that redirects to your paste?

Private Private paste aren't shown in recent listings.

Delete After When should we delete your paste?

Spam protection Type in the letters

https://burned.cc - Burn After Reading Website

Pastebin.vip

Query all subject information of students whose scores in each subject are greater than 80

Reply to "Query all subject information of students whose scores in each subject are greater than 80"