是按照subject和result排名次,对吗?select term,class,subject,studentid,result,(select count(trem) +1 as 名次 from tabel bb where (bb.subject = aa.subject) and (bb.result < aa.result)) as 名次 from tabel aa随手写的,你再验证一下.
其实原理一样,把我上面的sql语句再改一下就行了 select term,class,subject,studentid,result,(select count(trem) +1 as 名次 from tabel bb where (bb.term = aa.term) and (bb.class = aa.class) and (bb.subject = aa.subject) and (bb.studentid = aa.studentid) and (bb.result < aa.result)) as 名次 from tabel aa
能看懂吗? 这是一个嵌套查询,名次是通过统计表里面比当前记录成绩高的记录个数。 sorry!前面有个问题,STUDENTID好像不应该加入统计,否则的话都是1了 应该为:select term,class,subject,studentid,result,(select count(trem) +1 as 名次 from tabel bb where (bb.term = aa.term) and (bb.class = aa.class) and (bb.subject = aa.subject) and (bb.result < aa.result)) as 名次 from tabel aa
奥!这样啊,也容易,加一个group就行了: select term,class,subject,studentid,result,(select count(trem) +1 as 名次 from tabel bb where (bb.term = aa.term) and (bb.class = aa.class) and (bb.subject = aa.subject) group by result having (bb.result < aa.result) ) as 名次 from tabel aa(以前的result好像改为toalend了?我还用以前的)
谢谢!!!能再帮帮我吗? 为何会显示“至少一个参数没有被指定”?
我在Dataenvironment 中绑定三层结构的Command,三层分别用三个存储过程来实现的, 在第一层中设有一个参数。 第一层:CREATE PROCEDURE RStudent @StudentID VARCHAR(8) AS SELECT StudentID,... FROM Student WHERE StudentID = @StudentID 第二层: CREATE PROCEDURE SResume AS SELECT StudentID,ResumeID,.... FROM Resume 第三层: CREATE PROCEDURE RRelation AS SELECT ResumeID,FName... FROM Relationship VB中: If DataEnvironment1.rsStudent.State And adStateOpen Then DataEnvironment1.rsStudent.Close End If DataEnvironment1.Student Text1 DataReport19.Show 在此报表预览时就会出错。?
select term,class,subject,studentid,result,(select count(trem) +1 as 名次 from tabel bb where (bb.term = aa.term) and (bb.class = aa.class) and (bb.subject = aa.subject) group by result having (bb.result < aa.result) ) as 名次 from tabel aa报错:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
select term,class,subject,studentid,result,(select count(分数)+1 from (select distinct result as 分数 from tabel bb where (bb.term = aa.term) and (bb.class = aa.class) and (bb.subject = aa.subject) and (bb.result < aa.result)) cc ) as 名次 from tabel aa再试试?
第一件事搞定了。谢谢!!!第二件当我将后面两个存储过程换成查询后,其错误信息又变成了: “一般错误”,了。 若不要分层,下面存储过程是通过的。 CREATE PROCEDURE RStudent @StudentID VARCHAR(8) AS SELECT StudentID,... FROM Student WHERE StudentID = @StudentID 参数在报表预览前批定了。text1.text
还是看不懂你的意思。 像你这种情况,好像不用存储过程一般是这样处理的:DataEnvironment1.Commands("command1").CommandText = "SELECT StudentID,... FROM Student WHERE StudentID = '" & text1.text & "'" '构造查询语句 DataEnvironment1.Commands("command1").Execute '执行 DataEnvironment1.rsCommand1 '使用Recordset设计时command对象属性-->高级-->返回recordset打钩。
我的意思是这样的:
同一学期(term)同一专业(speciality)同一班级(class)的学生(studentid)的成绩(result)按成绩的高低排名次。
我的意思是这样的:
同一学期(term)同一专业(speciality)同一班级(class)的学生(studentid)的成绩(result)按成绩的高低排名次。
而且会有并列名次的现象,
现有一个表:
TERM CLASS SUBJECT STUDENTID RESULT 名次
2002 Y021 001 20020001 85
2002 Y021 001 20020002 75
2001 C021 002 20020001 66
2001 C021 002 20020002 75
2001 C021 002 20020003 66
名次栏的数字要通过程序计算来加上去的,就是如何计算的问题。
select term,class,subject,studentid,result,(select count(trem) +1 as 名次 from tabel bb where (bb.term = aa.term) and (bb.class = aa.class) and (bb.subject = aa.subject) and (bb.studentid = aa.studentid) and (bb.result < aa.result)) as 名次 from tabel aa
这是一个嵌套查询,名次是通过统计表里面比当前记录成绩高的记录个数。
sorry!前面有个问题,STUDENTID好像不应该加入统计,否则的话都是1了
应该为:select term,class,subject,studentid,result,(select count(trem) +1 as 名次 from tabel bb where (bb.term = aa.term) and (bb.class = aa.class) and (bb.subject = aa.subject) and (bb.result < aa.result)) as 名次 from tabel aa
InTerm Speciality classes SubjectID StudentID TotalEnd 名次
2002 Y021 001 A01 20020001 1500.0 1
2002 Y021 001 A01 20020002 1500.0 1
2002 Y021 001 A01 20020003 1400.0 3
的结果。
2002 Y021 001 A01 20020001 1500.0 1
2002 Y021 001 A01 20020002 1500.0 1
2002 Y021 001 A01 20020003 1400.0 2
谢谢!
select term,class,subject,studentid,result,(select count(trem) +1 as 名次 from tabel bb where (bb.term = aa.term) and (bb.class = aa.class) and (bb.subject = aa.subject) group by result having (bb.result < aa.result) ) as 名次 from tabel aa(以前的result好像改为toalend了?我还用以前的)
为何会显示“至少一个参数没有被指定”?
我在Dataenvironment 中绑定三层结构的Command,三层分别用三个存储过程来实现的,
在第一层中设有一个参数。
第一层:CREATE PROCEDURE RStudent
@StudentID VARCHAR(8)
AS
SELECT StudentID,... FROM Student
WHERE StudentID = @StudentID
第二层:
CREATE PROCEDURE SResume
AS
SELECT StudentID,ResumeID,.... FROM Resume
第三层:
CREATE PROCEDURE RRelation AS
SELECT ResumeID,FName... FROM Relationship
VB中:
If DataEnvironment1.rsStudent.State And adStateOpen Then
DataEnvironment1.rsStudent.Close
End If
DataEnvironment1.Student Text1
DataReport19.Show
在此报表预览时就会出错。?
你的三层结构是什么意思?用形状命令吗?如果是形状命令的话,可以单击右键选择层次信息,把里面的语句贴出来看一下。三个存储过程只有一个带参数,你是否正确指定了参数?
RStudent @StudentID = '????'另外感觉没必要什么都用存储过程,后面两个直接用视图就行了。
“一般错误”,了。
若不要分层,下面存储过程是通过的。
CREATE PROCEDURE RStudent
@StudentID VARCHAR(8)
AS
SELECT StudentID,... FROM Student
WHERE StudentID = @StudentID
参数在报表预览前批定了。text1.text
像你这种情况,好像不用存储过程一般是这样处理的:DataEnvironment1.Commands("command1").CommandText = "SELECT StudentID,... FROM Student WHERE StudentID = '" & text1.text & "'" '构造查询语句
DataEnvironment1.Commands("command1").Execute '执行
DataEnvironment1.rsCommand1 '使用Recordset设计时command对象属性-->高级-->返回recordset打钩。
最简单的方法:
前提:三个表都应包含一个关键字,用来唯一的标识一个人,其中基本情况表的个人编码不应重复(主关键字)在SqlServer企业管理器中,新建一个视图,把三张表都添加进去,在查询设计器视图中,通过拖动字段名的方法,建立表之间的联系,然后就可以选择需要的字段名、条件、排序规则,中间有一个格子,里面自动生成sql语句,copy出来就能用的,或者保存视图,就能像操作表一样的调用它了。我只能提醒一下,你自己还要好好学啊,这些都是最基础的东西。