现有两表,xsxx.db(内有字段:学号,年级,班级,……),result.db(内有字段:学号,学期,课程名称,成绩……),我欲实现输入一学生学号,就可得到其在该班依总成绩排名所得名次。
我曾经问过关于怎样得到名次的问题,经人点拨,编写代码如下,但始终有错误,不知应怎样实现,望各位大鸟帮忙,不甚感激!急用!!
(假设该学生学号为:99104413, 年级为:99级, 学期为:第一学期, 班级为:四班)方法一:
select count(*)+1 as 名次
from (select 学号,sum(成绩) as 总成绩 from result group by 学号) result1,(select 学号,sum(成绩) as 总成绩 from result group by 学号) result2,
xsxx,result
where xsxx.学号=result.学号 and 年级='99级' and 学期='第一学期' and xsxx.班级='四班' and result1.总成绩 > result2.总成绩 and result2.学号='99104413'方法二:
select count(*)+1 as 名次 from (select 学号,sum(成绩) as 总成绩 from result group by 学号) result1
where (总成绩<(select 学号,sum(成绩) as 总成绩 from result,xsxx
where xsxx.学号=result.学号 and 年级='99级' and 学期='第一学期' and xsxx.班级='四班' and result1.总成绩 > result2.总成绩 and result2.学号='99104413'))方法三:
select count(*)+1 as 名次
from xsxx
where (学号 in
(select 学号
from result result1
group by 学号
having (exists
(select 学号
from result result2,xsxx
group by 学号
having xsxx.学号=result2.学号 and 年级='99级' and 学期='第一学期' and xsxx.班级='四班' and result2.学号='99104413' and sum(result1.成绩)>sum(result2.成绩)))))解决即给分!!
我曾经问过关于怎样得到名次的问题,经人点拨,编写代码如下,但始终有错误,不知应怎样实现,望各位大鸟帮忙,不甚感激!急用!!
(假设该学生学号为:99104413, 年级为:99级, 学期为:第一学期, 班级为:四班)方法一:
select count(*)+1 as 名次
from (select 学号,sum(成绩) as 总成绩 from result group by 学号) result1,(select 学号,sum(成绩) as 总成绩 from result group by 学号) result2,
xsxx,result
where xsxx.学号=result.学号 and 年级='99级' and 学期='第一学期' and xsxx.班级='四班' and result1.总成绩 > result2.总成绩 and result2.学号='99104413'方法二:
select count(*)+1 as 名次 from (select 学号,sum(成绩) as 总成绩 from result group by 学号) result1
where (总成绩<(select 学号,sum(成绩) as 总成绩 from result,xsxx
where xsxx.学号=result.学号 and 年级='99级' and 学期='第一学期' and xsxx.班级='四班' and result1.总成绩 > result2.总成绩 and result2.学号='99104413'))方法三:
select count(*)+1 as 名次
from xsxx
where (学号 in
(select 学号
from result result1
group by 学号
having (exists
(select 学号
from result result2,xsxx
group by 学号
having xsxx.学号=result2.学号 and 年级='99级' and 学期='第一学期' and xsxx.班级='四班' and result2.学号='99104413' and sum(result1.成绩)>sum(result2.成绩)))))解决即给分!!
解决方案 »
- 字符串转换问题?
- 这些天为什么提问的人越来越少了呀,我来问,分不多了大家帮忙
- Htonl,Htons,nltoh,nstoh是如何使用的?
- 请教我这条日期查询语句那里出错了。
- 向数据库中保存doc文档的问题?
- 如何访问foxpro6的表单的属性及相关内容
- 两个局域网中的英特网用户要怎么样才能通信。给点思路吧,或者要看什么书也行。
- 用流做图片的合并!然后在解出每张图片?
- 怎么用DELPHI做一个网络抓包程序,急需啊!高手指教
- EHLIB组件哪里有?
- 请教一下,如何判断SQLServer2000中数据库master是否存在某个存储过程?
- Form1里有一个button1,Form2里有一个Edit1,
难道就没有大鸟能帮我吗?
:(
Query1.close;
Query1.SQL.Clear;
Query1.SQL.Add('select result.学号,sum(result.成绩) as 总成绩 from xsxx,result
where (xsxx.学号=result.学号) and (result.学期='''+ '第一学期'''+') group by result.学号 order by 总成绩 desc');
Query1.Prepare;
Query1.Open;
if Query1.Locate('学号','99104413',[]) then i:= Query1.RecNo;i值即为名次!
from xsxx a,
(select 学号,sum(成绩) as 总成绩 from result group by 学号) b,
(select 学号,sum(成绩) as 总成绩 from result group by 学号) c,
(select sum(成绩) as 总成绩 from result group by 总成绩) d
where a.学号=b.学号 and a.年级='99级' and b.学期='第一学期' and a.班级='四班' and
c.总成绩 >=b.总成绩 and d.总成绩=c.总成绩 and d.总成绩=b.总成绩
order by count(c.学号)由于手头没有库,只能空想
你试试吧,有什么问题明天帮你解决。
select count(c.学号)-count(d.总成绩)+1 as 名次
from xsxx a,
(select 学号,sum(成绩) as 总成绩 from result group by 学号) b,
(select 学号,sum(成绩) as 总成绩 from result group by 学号) c,
(select sum(成绩) as 总成绩 from result group by 总成绩) d
where a.学号=b.学号 and a.年级='99级' and b.学期='第一学期' and a.班级='四班' and
c.总成绩 >=b.总成绩 and d.总成绩=c.总成绩 and d.总成绩=b.总成绩
order by count(c.学号))-count(d.总成绩)+1