SQL子查询

CREATE TABLE t_grade(
g_id INT PRIMARY KEY AUTO_INCREMENT,
g_name VARCHAR(20),
g_subject VARCHAR(20),
g_grade INT 




);


INSERT INTO t_grade(g_name,g_subject,g_grade) VALUES
('张三','语文',90),('张三','数学',90),('张三','英语',90),
('李四','语文',67),('李四','数学',65),('李四','英语',53),
('王五','语文',84),('王五','数学',76),('王五','英语',74),
('赵六','语文',53),('赵六','数学',87),('赵六','英语',83),
('田七','语文',98),('田七','数学',93),('田七','英语',64);


SELECT g_subject,MAX(g_grade) FROM t_grade GROUP BY g_subject


-- 显示比李四语文考的更好的学生信息
SELECT * FROM t_grade WHERE g_subject = '语文' AND g_grade>
(SELECT g_grade FROM t_grade WHERE g_name='李四' AND g_subject ='语文')


-- 显示和王五英语同分的学生信息
SELECT * FROM t_grade WHERE g_subject ='英语' AND g_name != '王五' AND g_grade=
(SELECT g_grade FROM t_grade WHERE g_name ='王五' AND g_subject='英语')


-- 显示每门科目最高分的学生信息
SELECT * FROM t_grade g1 WHERE g_grade IN
(SELECT MAX(g_grade) FROM t_grade g2 WHERE g1.g_subject = g2.g_subject GROUP BY g_subject)