查询男生和女生成绩前三

来源：互联网发布：网络关系成瘾编辑：程序博客网时间：2024/04/30 08:39

CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
studentName VARCHAR(20),
sex VARCHAR(20),
subjects VARCHAR(20),
grade INT ,
class VARCHAR(20)
)DEFAULT CHARSET=utf8;

INSERT INTO student(studentName,sex,subjects,grade,class)VALUES('张三','男','语文',80,'J112'),('李四','男','语文',80,'J112'),('王五','男','语文',70,'J112'),
('赵六','女','语文',90,'J112'),('田七','女','语文',50,'J112'),('王八','女','语文',89,'J112'),('九妹','女','语文',85,'J112'),
('始皇','女','语文',84,'J112'),('萧十一','女','语文',30,'J112'),('十二生肖','男','语文',90,'J112'),('臭十三','女','语文',83,'J112');

SELECT*FROM student WHERE id IN(
SELECT id FROM student WHERE sex='男' ORDER BY grade DESC LIMIT 0,3
UNION
SELECT id FROM student WHERE sex='女' ORDER BY grade DESC LIMIT 0,3);

SELECT *FROM student s1 WHERE (SELECT COUNT(*) FROM student s2 WHERE s1.sex=s2.sex AND s2.grade>=s1.grade)<4 ORDER BY s1.sex ASC,s1.grade DESC

0 0