两个表
表一:Mark
字段 sno(学号) Tid(教师号) CourseId(课程号) degree(作业次数) (分数)ClassId(班级号)
表二:Student
字段 sno name classid 现在我想根据 Tid,ClassId进行查询(degree不知,需要在数据库中查询);查询的一般结果为:
sno degree
1 1 90
1 2 85
1 3 95
2 1 80
2 2 86
2 3 89
3 1 75
... ... ...
我现在的要求是:
变成:
sno 1 2 3 ... AVG
1 90 85 95 ... 90
2 80 85 89 ... 85
... ... ... ... ... ...
已经有朋友给了--sql 2000静态SQL,指degree只有1,2,3三种情况。
select sno,
max(case degree when 1 then else 0 end) 1,
max(case degree when 2 then else 0 end) 2,
max(case degree when 3 then else 0 end) 3,
avg()
group by sno--sql 2000动态SQL,指degree不止1,2,3三种情况。
declare @sql varchar(8000)
set @sql = 'select sno '
select @sql = @sql + ' , max(case degree when ''' + degree + ''' then else 0 end) [' + degree + ']'
from (select distinct degree from tb) as a
set @sql = @sql + ' from tb group by degree'
exec(@sql) --如果degree是INT型的。
declare @sql varchar(8000)
set @sql = 'select sno '
select @sql = @sql + ' , max(case degree when ''' + cast(degree as varchar) + ''' then else 0 end) [' + cast(degree as varchar) + ']'
from (select distinct degree from tb) as a
set @sql = @sql + ' from tb group by degree'
exec(@sql) 我写的是:
use homework
declare @sql varchar(8000)
set @sql='select sno '
select @sql=@sql+ ', max(case degree when '''+degree+''' then Mark. else 0 end) ['+ degree+']'
from (select distinct degree from Mark)as a
set @sql=@sql +' from Mark group by sno'
print @sql
exec(@sql)提示错误:
消息 245,级别 16,状态 1,第 4 行
在将 varchar 值 '' then Mark. else 0 end) [' 转换成数据类型 int 时失败。
表一:Mark
字段 sno(学号) Tid(教师号) CourseId(课程号) degree(作业次数) (分数)ClassId(班级号)
表二:Student
字段 sno name classid 现在我想根据 Tid,ClassId进行查询(degree不知,需要在数据库中查询);查询的一般结果为:
sno degree
1 1 90
1 2 85
1 3 95
2 1 80
2 2 86
2 3 89
3 1 75
... ... ...
我现在的要求是:
变成:
sno 1 2 3 ... AVG
1 90 85 95 ... 90
2 80 85 89 ... 85
... ... ... ... ... ...
已经有朋友给了--sql 2000静态SQL,指degree只有1,2,3三种情况。
select sno,
max(case degree when 1 then else 0 end) 1,
max(case degree when 2 then else 0 end) 2,
max(case degree when 3 then else 0 end) 3,
avg()
group by sno--sql 2000动态SQL,指degree不止1,2,3三种情况。
declare @sql varchar(8000)
set @sql = 'select sno '
select @sql = @sql + ' , max(case degree when ''' + degree + ''' then else 0 end) [' + degree + ']'
from (select distinct degree from tb) as a
set @sql = @sql + ' from tb group by degree'
exec(@sql) --如果degree是INT型的。
declare @sql varchar(8000)
set @sql = 'select sno '
select @sql = @sql + ' , max(case degree when ''' + cast(degree as varchar) + ''' then else 0 end) [' + cast(degree as varchar) + ']'
from (select distinct degree from tb) as a
set @sql = @sql + ' from tb group by degree'
exec(@sql) 我写的是:
use homework
declare @sql varchar(8000)
set @sql='select sno '
select @sql=@sql+ ', max(case degree when '''+degree+''' then Mark. else 0 end) ['+ degree+']'
from (select distinct degree from Mark)as a
set @sql=@sql +' from Mark group by sno'
print @sql
exec(@sql)提示错误:
消息 245,级别 16,状态 1,第 4 行
在将 varchar 值 '' then Mark. else 0 end) [' 转换成数据类型 int 时失败。
set @sql='select sno '
select @sql=@sql+ ', max(case degree when '''+ltrim(degree)+''' then Mark. else 0 end) ['+ ltrim(degree)+']'
from (select distinct degree from Mark)as a
set @sql=@sql +' from Mark group by sno'
exec(@sql) 加ltrim()
insert into Mark select 1,1,90
insert into Mark select 1,2,85
insert into Mark select 1,3,95
insert into Mark select 2,1,80
insert into Mark select 2,2,86
insert into Mark select 2,3,89
insert into Mark select 3,1,75declare @sql varchar(8000),@id varchar(8000)
select @id=isnull(@id+'+','')+''+ltrim(degree),@sql=isnull(@sql+',','')+'max(case degree when '''+ltrim(degree)+''' then Mark. else 0 end) ['+ ltrim(degree)+']'
from (select distinct degree from Mark)as a
exec('select sno,'+@sql+',avg() as Avg from Mark group by sno')sno 1 2 3 Avg
1 90 85 95 90
2 80 86 89 85
3 75 0 0 75
貌似LS是个左撇子,我就一直用rtrim()!
hoho!
谢谢,我明天再给你加一百分,不过请你帮帮忙,你有没有发现,你根本就没有用Student 表,和两个查询条件Tid,ClassId
不过一般比较容易多出右边的空格,heihei