declare @sql varchar(8000) select @sql=''select @sql=@sql+',sum(case when stsubject='''+stsubject+''' and stpd=''第一学期'' then ststm else 0 end) as ['+stsubject+'1]' +',sum(case when stsubject='''+stsubject+''' and stpd=''第二学期'' then ststm else 0 end) as ['+stsubject+'2]' +',sum(case when stsubject='''+stsubject+''' then ststm else 0 end) as ['+stsubject+']' from 表 group by stsubject select @sql='select stno'+@sql+' from 表 group by stno' exec(@sql)
declare @sql varchar(8000) select @sql=''select @sql=@sql+',sum(case when stsubject='''+stsubject+''' and stpd=''第一学期'' then ststm else 0 end) as ['+stsubject+'1]' +',sum(case when stsubject='''+stsubject+''' and stpd=''第二学期'' then ststm else 0 end) as ['+stsubject+'2]' +',sum(case when stsubject='''+stsubject+''' then ststm else 0 end) as ['+stsubject+']' from dmstm group by stsubject select @sql='select stno'+@sql+' from dmstm group by stno' exec(@sql)
create table dmstm ( stno varchar(20), stsubject varchar(20), sttype varchar(10), styear varchar(20), stpd varchar(10), ststm decimal(12,2) ) --部分数据 insert dmstm select '9990101','地理','期末','20042005','第一学期','75' union select '9990101','化学','期末','20042005','第一学期','87' union select '9990101','生物','期末','20042005','第一学期','85.5' union select '9990101','数学','期末','20042005','第一学期','85' union select '9990101','外语','期末','20042005','第一学期','70' union select '9990101','物理','期末','20042005','第一学期','86' union select '9990101','语文','期末','20042005','第一学期','64' union select '9990101','政治','期末','20042005','第一学期','48' union select '9990101','计算机原理','期末','20042005','第一学期','70' union select '9990101','C语言编程','期末','20042005','第一学期','75' insert dmstm select '9990101','地理','期末','20042005','第二学期','75' union select '9990101','化学','期末','20042005','第二学期','87' union select '9990101','生物','期末','20042005','第二学期','85.5' union select '9990101','数学','期末','20042005','第二学期','85' union select '9990101','外语','期末','20042005','第二学期','70' union select '9990101','物理','期末','20042005','第二学期','86' union select '9990101','语文','期末','20042005','第二学期','64' union select '9990101','政治','期末','20042005','第二学期','48' union select '9990101','计算机原理','期末','20042005','第二学期','70' union select '9990101','C语言编程','期末','20042005','第二学期','75' --查询 declare @sql varchar(8000) select @sql=''select @sql=@sql+',sum(case when stsubject='''+stsubject+''' and stpd=''第一学期'' then ststm else 0 end) as ['+stsubject+'1]' +',sum(case when stsubject='''+stsubject+''' and stpd=''第二学期'' then ststm else 0 end) as ['+stsubject+'2]' +',sum(case when stsubject='''+stsubject+''' then ststm else 0 end) as ['+stsubject+']' from dmstm group by stsubject select @sql='select stno'+@sql+' from dmstm group by stno' exec(@sql) --删除测试环境 drop table dmstm--结果 /* stno C语言编程1 C语言编程2 C语言编程 地理1 地理2 地理 化学1 化学2 化学 计算机原理1 计算机原理2 计算机原理 生物1 生物2 生物 数学1 数学2 数学 外语1 外语2 外语 物理1 物理2 物理 语文1 语文2 语文 政治1 政治2 政治 -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 9990101 75.00 75.00 150.00 75.00 75.00 150.00 87.00 87.00 174.00 70.00 70.00 140.00 85.50 85.50 171.00 85.00 85.00 170.00 70.00 70.00 140.00 86.00 86.00 172.00 64.00 64.00 128.00 48.00 48.00 96.00 */
select @sql=''select @sql=@sql+',sum(case when stsubject='''+stsubject+''' and stpd=''第一学期'' then ststm else 0 end) as ['+stsubject+'1]'
+',sum(case when stsubject='''+stsubject+''' and stpd=''第二学期'' then ststm else 0 end) as ['+stsubject+'2]'
+',sum(case when stsubject='''+stsubject+''' then ststm else 0 end) as ['+stsubject+']'
from 表 group by stsubject
select @sql='select stno'+@sql+' from 表 group by stno'
exec(@sql)
select @sql=''select @sql=@sql+',sum(case when stsubject='''+stsubject+''' and stpd=''第一学期'' then ststm else 0 end) as ['+stsubject+'1]'
+',sum(case when stsubject='''+stsubject+''' and stpd=''第二学期'' then ststm else 0 end) as ['+stsubject+'2]'
+',sum(case when stsubject='''+stsubject+''' then ststm else 0 end) as ['+stsubject+']'
from dmstm group by stsubject
select @sql='select stno'+@sql+' from dmstm group by stno'
exec(@sql)
(
stno varchar(20),
stsubject varchar(20),
sttype varchar(10),
styear varchar(20),
stpd varchar(10),
ststm decimal(12,2)
)
--部分数据
insert dmstm
select '9990101','地理','期末','20042005','第一学期','75' union
select '9990101','化学','期末','20042005','第一学期','87' union
select '9990101','生物','期末','20042005','第一学期','85.5' union
select '9990101','数学','期末','20042005','第一学期','85' union
select '9990101','外语','期末','20042005','第一学期','70' union
select '9990101','物理','期末','20042005','第一学期','86' union
select '9990101','语文','期末','20042005','第一学期','64' union
select '9990101','政治','期末','20042005','第一学期','48' union
select '9990101','计算机原理','期末','20042005','第一学期','70' union
select '9990101','C语言编程','期末','20042005','第一学期','75'
insert dmstm
select '9990101','地理','期末','20042005','第二学期','75' union
select '9990101','化学','期末','20042005','第二学期','87' union
select '9990101','生物','期末','20042005','第二学期','85.5' union
select '9990101','数学','期末','20042005','第二学期','85' union
select '9990101','外语','期末','20042005','第二学期','70' union
select '9990101','物理','期末','20042005','第二学期','86' union
select '9990101','语文','期末','20042005','第二学期','64' union
select '9990101','政治','期末','20042005','第二学期','48' union
select '9990101','计算机原理','期末','20042005','第二学期','70' union
select '9990101','C语言编程','期末','20042005','第二学期','75' --查询
declare @sql varchar(8000)
select @sql=''select @sql=@sql+',sum(case when stsubject='''+stsubject+''' and stpd=''第一学期'' then ststm else 0 end) as ['+stsubject+'1]'
+',sum(case when stsubject='''+stsubject+''' and stpd=''第二学期'' then ststm else 0 end) as ['+stsubject+'2]'
+',sum(case when stsubject='''+stsubject+''' then ststm else 0 end) as ['+stsubject+']'
from dmstm group by stsubject
select @sql='select stno'+@sql+' from dmstm group by stno'
exec(@sql)
--删除测试环境
drop table dmstm--结果
/*
stno C语言编程1 C语言编程2 C语言编程 地理1 地理2 地理 化学1 化学2 化学 计算机原理1 计算机原理2 计算机原理 生物1 生物2 生物 数学1 数学2 数学 外语1 外语2 外语 物理1 物理2 物理 语文1 语文2 语文 政治1 政治2 政治
-------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
9990101 75.00 75.00 150.00 75.00 75.00 150.00 87.00 87.00 174.00 70.00 70.00 140.00 85.50 85.50 171.00 85.00 85.00 170.00 70.00 70.00 140.00 86.00 86.00 172.00 64.00 64.00 128.00 48.00 48.00 96.00
*/
老大 我佩服你!
然后学年总评的成绩=第一学期的学期的总评成绩*0.4+第二学期的学期的总评成绩*0.6
................................................
而且这里面还要注意的是:如果某一年的学科为14科,
而每一个学期每一门课的平时的测验次数超过3次(我们假象为5次吧),
这个时候
展开的sql语句的长度好象会超过8000吧?
-------------------------------------------------------------------------
可以用多个varchar(8000)的字符串变量分别拼装SQL语句的一部分,最后
exec @var1+@var2+@var3+...+@varn
参看
http://community.csdn.net/Expert/topic/4143/4143434.xml?temp=.2563898