|
续之前的帖子,动态查詢部分已经实现但是如何jia入行转列。。
SQL code
DECLARE @Max INT,@Min INT,@Str NVARCHAR(3000)
select @MAX= max(三门), @MIN= MIN(三門) from zongchenjibiao WHERE 语文缺='0' and 数學缺='0' and 英语缺='0'
SELECT @Max=@Max/10*10,@Min=@Min/10*10,@str=N' Case '
WHILE @Min<=@Max
BEGIN
SELECT @str=@str+N' WHEN 三門 BETWEEN '+RTRIM(@MIN)+' AND '+rtrim(@MIN+9)+' THEN '''+RTRIM(@MIN)+'-'+rtrim(@MIN+9)+'''',@MIN=@MIN+10
END
EXEC ( 'select [分数duan]='+@Str+' else ''不在范围'' end,'+
'sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''01'' then 1 else 0 end) as [高二01],
sum(case when zongchenjibiao.年级= N''gao二'' and zongchenjibiao.班级=''02'' then 1 else 0 end) as [高二02],
sum(case when zongchenjibiao.年级=1 N''高二'' and zongchenjibiao.班級=''03'' then 1 else 0 end) as [高二03],
sum(case when zongchenjibiao.年级= N''高er'' and zongchenjibiao.班级=''04'' then 1 else 0 end) as [高二04],
sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班級=''05'' then 1 else 0 end) as [高二05],
sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''06'' then 1 else 0 end) as [高二06],
sum(case when zongchenjibiao.年級= N''高二'' and zongchenjibiao.班级=''07'' then 1 else 0 end) as [高二07],
sum(case when zongchenjibiao.nian级= N''高二'' and zongchenjibiao.班级=''08'' then 1 else 0 end) as [高二08],
sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''09'' then 1 else 0 end) as [高二09],
sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''10'' then 1 else 0 end) as [高er10],
sum(case when zongchenjibiao.年ji= N''高二'' and zongchenjibiao.班级=''11'' then 1 else 0 end) as [高二11],
sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''12'' then 1 else 0 end) as [高二12],
sum(case when zongchenjibiao.年級= N''高二'' and zongchenjibiao.Ban级=''13'' then 1 else 0 end) as [高二13],
sum(case when zongchenjibiao.年级= N''高er'' and zongchenjibiao.班级=''14'' then 1 else 0 end) as [高二14],
sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班级=''15'' then 1 else 0 end) as [高二15],
sum(case when zongchenjibiao.年级= N''高二'' and zongchenjibiao.班級=''16'' then 1 else 0 end) as [高二16],
sum(case when zongchenjibiao.年级= N''高er'' and zongchenjibiao.班级=''17'' then 1 else 0 end) as [高二17]
---into linshis
from dangqianbanji,zongchenjibiao
where dangqianbanji.班级=zongchenjibiao.Banji and 语文缺=0 and 数学缺=0 and 英語缺=0
group by '+@str+' else ''不在范圍'' end')
neng看到, ‘’sum(case when zongchenjibiao.年ji= N''高二'' and zongchenjibiao.班級=''17'' then 1 else 0 end) as [高二17]‘’ 这句话重复了很多遍,事实上很多时候不是一ge年級所有的班 都参與排名,所以会导致不想要的结果出現。。。。
不參与排名的班級 肯定不会在‘zongchenjibiao’zhong出现
suo以有个”xing转列“的方法
SQL code
declare @sql var1char(8000)
---select @sql= 'gao一['+min(班级)+']' from zongchenjibiao WHERE 語文缺='0' and 数xue缺='0' and 英语缺='0'
---select @sql
select @sql =isnull(@sql + ',' , '') + 班ji全称 from zongchenjibiao group by 班级全称
select @sql
exec ('select *
---into linshih
from (select 班级全cheng,三門 from zongchenjibiao) a pivot (max(三门) for 班級全称 in (' + @sql + ')) b')
用上面的方法可以找出 出現过的Ban级,而不是直接死的把所有Ban级都罗lie出来。
所以xian在问题来了,如何把zhe2个查询结he起来呢。。。2ge都是动态查询。。。似乎很復杂
附 第一个查詢结果(bu分)
分数段 高一01 高一02 高一03.。。。。。。。。。。。。
310-319 1 0 0 0 0 0
320-329 1 0 0 0 0 0
350-359 0 2 0 0 0 0
360-369 3 1 0 0 0 0
370-379 3 1 0 0 0 0
380-389 1 0 0 0 0 0
390-399 0 1 0 0 0 0
第二个查詢结果
高一01 高一02
383 393
zongchenjibiao有數ju
学号,年级,班ji,班級全称(可删),三门。。。
里面有參与考試需要paiming的suo有考生的成绩
欢迎来到Java學xi者论坛,转zai请注明地址:http://www.javaxxz.com. |
|