假设有两个表:
成绩表(Record):学号(Number)<主键>、成绩(Score)
学生表(Student):学号(Number)<主键>、姓名(Name)、班级(Class)
用SQL语句实现下面查询。
1)每个班最高分数与最低分数的差(最高分-最低分)
2)每个班的分数第二名的姓名
成绩表(Record):学号(Number)<主键>、成绩(Score)
学生表(Student):学号(Number)<主键>、姓名(Name)、班级(Class)
用SQL语句实现下面查询。
1)每个班最高分数与最低分数的差(最高分-最低分)
2)每个班的分数第二名的姓名
解决方案 »
- 用Scanner输入的问题
- 求java中日期范围正则表达式
- 鄙人想请各位大侠给出,返回类型不一样的重载方法的代码
- java对象数组排序
- JFrame关闭问题????
- 如何将string类转换成流,最后将其输入到指定文件中?
- 汗颜怀疑自己这么多年一直把i++和++i给记反了以及C和Java编译对比
- java 指定端口 访问某个文件目录
- 我的JB的Componet栏中为什么没有“JBCL”这个选项卡?谢谢
- 在构造器内唯一能够安全调用的那些方法是基类中的final或者private方法,这些方法不能被覆盖,因此也就不会出现令人惊讶的问题。
- java中重载函数中的构造方法
- 怎么样才能在java的文件操作中用到相对路径
(
select max(Score) from Record,Student where Record.Number=Student.number and Student.Class=s.Class
and Score<>(select max(Score) from Record,Student where Record.Number=Student.number and Student.Class=s.Class)
)
group by class,name另外,Record,Score,Class这三个是关键字(有些数据库是有些数据库不是),最好改成其他名字
group by s.class;
group by s.class) and Record.number=student.number group by student.class
1)select max(Score)-min(Score) from record r,student s where r.number=s.number
group by s.class;
create database testdemo
go
use testdemo
go
create table Record
(
Number int primary key,
Score numeric(3,1)
)
go
create table Student
(
Number int primary key,
Name varchar(20),
class varchar(20)
)
go
--插入数据
insert into Record
select 1,55
union
select 2,33
union
select 3,45
union
select 4,45
union
select 5,35
insert into Student
select 1,'zz','class1'
union
select 2,'qq','class1'
union
select 3,'fdsf','class2'
union
select 4,'asdfds','class2'
union
select 5,'sdf','class2'
--第一题
select max(score)-min(score) as 最高分数与最低分数的差, class
from Record r inner join Student s on r.number=s.number
group by class
--第二题.....
(select Class, max(r2.Score) as max_score from Record as r2 join Student as s2 on r2.Number = s2.Number group by s2.Class) as c on s.Class = c.Class and
r.Score <> c.max_score) as d group by d.Class) as g on e.Class = g.Class and e.Score = g.max2th
group by s.class;
2.
select x.Name as Numbertwo,x.Class from Student x,Record y where x.Number=y.Number and y.Score in
(select Max(Score) from Student,Record where Student.Number=Record.Number and Student.Class=x.Class and Record.Score not in (Select Max(Score) from Student,Record where Student.Number=Record.Number and Student.Class=x.Class))
group by x.Name,x.Class
第二题select Name,Score from (
select Score, Class, Name from Record r inner join student s on s.number = r.number group by Class having Score <> max(Score)) temp group by temp.Class having Score = max(Score);
]蜗壳网: http://www.wokenet.com/
笔记本电脑新闻,行情,导购,评测,论坛,做最专业的数码产品搜索引擎