CREATE Procedure aaaa
@classstr Varchar(10)
as
declare @sql varchar(8000)
set @sql = 'select studentnum as 学号,studentname as 姓名'
select @sql = @sql + ',max(case coursename when '''+coursename+''' then achieve else 0 end) ['+coursename+']'
from (select distinct coursename from coursetable_view where classidnum=@classstr) as a
select @sql = @sql+' from achievement_view where studentid in (select idnum from student where classid='''+@classstr+''' ) group by studentnum,studentname'exec(@sql)
@classstr Varchar(10)
as
declare @sql varchar(8000)
set @sql = 'select studentnum as 学号,studentname as 姓名'
select @sql = @sql + ',max(case coursename when '''+coursename+''' then achieve else 0 end) ['+coursename+']'
from (select distinct coursename from coursetable_view where classidnum=@classstr) as a
select @sql = @sql+' from achievement_view where studentid in (select idnum from student where classid='''+@classstr+''' ) group by studentnum,studentname'exec(@sql)
解决方案 »
- 关于sql语句效率的问题
- 关于日期的一个sql语句
- 导入数据 求一可行有效方法
- 怎样跟踪查询的物理读数与逻辑读数
- 菜鸟问题,想到我头疼的问题
- 有一个24小时运行的数据库中有一张表,每秒新增20条左右的数据。我现在需要对这张表做统计的操作,比如某个时间段(时日月)的统计。
- [紧急求助]合并复制错误(MSSQL_REPL-2147201019)
- 求UPDATE类型的触发器
- 什么是多线程查询,查到一点就显示一点给用户
- 编写带参数的存储过程,查询指定教学楼,指定教室,指定时间段的教室使用情况,或者有课,或者有讲座、或者有活动、或者空闲。
- DELPHI ADO连接MSSQLSERVER,照片字段image,用DBIMAGE关联照片字段,active时显示"bitmap image is no valid"
- 兩個字段的值組成表記錄的唯一標誌這樣的表的主鍵和索引怎麼設置???
还有我还想加个总分和平均分字段啊!麻烦了!
@classstr int
as
declare @sql varchar(8000)
set @sql = 'select studentnum as 学号,studentname as 姓名'
select @sql = @sql + ',max(case coursename when '''+coursename+''' then achieve else 0 end)
['+coursename+']'
from (select distinct coursename from coursetable_view where classidnum=@classstr) as a
select @sql = @sql+' from achievement_view where studentid in
(select idnum from student where classid='+@classstr+' ) group by studentnum,studentname'exec(@sql)你的两个表的classid和classidnum列是什么类型???
你的coursetable_view 表是????
是课程表的视图:
CREATE VIEW COURSETABLE_VIEW
AS SELECT dbo.coursetable.*, dbo.Class.Classname AS Classname,
dbo.weektab.weekname AS Weekname, dbo.nodeset.nodename AS Nodename,
dbo.coursename.coursename AS Coursename,
dbo.teacher.teachername AS Teachername
FROM dbo.Class INNER JOIN
dbo.coursetable ON dbo.Class.idnum = dbo.coursetable.classidnum INNER JOIN
dbo.weektab ON dbo.coursetable.weekidnum = dbo.weektab.idnum INNER JOIN
dbo.nodeset ON dbo.coursetable.nodeidnum = dbo.nodeset.idnum INNER JOIN
dbo.coursename ON
dbo.coursetable.courseidnum = dbo.coursename.idnum INNER JOIN
dbo.teacher ON dbo.coursetable.teacheridnum = dbo.teacher.idnum
我再重复一遍,谢谢
我想问问我认为第一个参数是决定班级的课程的,第二个是决定班级人数的啊?为什么你帮我改了第二个参数呢?还有 as a 是把选出来的所有课程字段都 as a 了,还是其他的什么请帮我讲解讲解。谢谢你Pengdali,为什么你总是出现的那么及时啊!!!!
还有我还想加个总分和平均分字段啊!麻烦了!
from (select distinct coursename from coursetable_view where classidnum=@classstr) as a这句吗??是决定班级的课程
--------------------------------------
为什么你帮我改了第二个参数呢?还有 as a 是把选出来的所有课程字段都 as a 了,
那你就:CREATE Procedure aaaa
@classstr char(3)
as
declare @sql varchar(8000)
set @sql = 'select studentnum as 学号,studentname as 姓名'
select @sql = @sql + ',max(case coursename when '''+coursename+''' then achieve else 0 end)
['+coursename+']'
from (select distinct coursename from achievement_view where studentid in (select idnum from student where classid=@classstr)) a
select @sql = @sql+' from achievement_view where studentid in
(select idnum from student where classid='''+@classstr+''' ) group by studentnum,studentname'exec(@sql)
CREATE Procedure aaaa
@classstr char(3)
as
declare @sql varchar(8000)
set @sql = 'select studentnum as 学号,studentname as 姓名'
select @sql = @sql + ',max(case coursename when '''+coursename+''' then achieve else 0 end)
['+coursename+']'
from (select distinct coursename from achievement_view where studentid in (select idnum from student where classid=@classstr)) a
select @sql = @sql+',sum(achieve) 总分,avg(achieve) 平均分 from achievement_view where studentid in
(select idnum from student where classid='''+@classstr+''' ) group by studentnum,studentname'exec(@sql)
大力再帮帮我吧,就剩最后一点了。谢谢了!
我的意思是把它改成(select idnum from student where classid=006)就好,请问该如何解决,
急啊,大哥
真诚的感谢大力大哥,谢谢,下班后揭贴。
为什么要转成int????
我中午的时候实在是很愚,我就在查询分析器里边改我的存储过程,最后终于好了,至于原因,我现在也不是很清楚,谢谢大力。而且我现在把求和和平均分也给去了,因为平均分它计算的时候不把0分算在里边,所以算出来的不是很标准,而总分要是有补考的情况在的话,它的总分就多了(除非补考的成绩是修改原来的成绩),
再次感谢大力。