create table t_class(
name char(10), yuwen int,shuxue int,yingyu int, xueqi int
);
insert into t_class values('张三',70,100,85,1);
insert into t_class values('张三',54,65,82,2);
insert into t_class values('张三',23,80,75,3);
insert into t_class values('张三',98,89,89,4);
insert into t_class values('张三',34,76,85,5);
insert into t_class values('李四',70,100,85,5);
insert into t_class values('李四',54,65,82,4);
insert into t_class values('李四',23,80,75,2);
insert into t_class values('李四',98,89,89,1);
insert into t_class values('李四',34,76,85,3);
insert into t_class values('王二',71,95,75,1);
insert into t_class values('王二',55,61,84,2);
insert into t_class values('王二',73,82,78,3);
insert into t_class values('王二',88,80,83,4);
insert into t_class values('王二',94,77,82,5);
如下SQL获取每个学期语文成绩排名第二的学生的成绩单信息。
select t4.* from t_class t4, (
select t1.name, t1.xueqi, count(*)
from t_class t1, t_class t2
where t1.yuwen<=t2.yuwen and t1.xueqi=t2.xueqi
group by 1,2 having count(*) =2 --排名第二的情况
) t5
where t4.name=t5.name and t4.xueqi=t5.xueqi
order by t4.xueqi
添加新评论2 条评论
2017-06-20 16:59
2017-06-20 16:37