日期:2014-05-17 浏览次数:20628 次
CREATE PROCEDURE myproc
@conditions varchar(1000)
AS
declare @tsql varchar(6000),@njbm varchar(20),@bjbm varchar(20),@kskemu varchar(1000)
select @kskemu=isnull(@kskemu+',','') +''+kemu+','+kemu+'年名,'+kemu+'班名'+'' from(select distinct kemu from cj) t
set @kskemu='ksid,班级,学号,姓名,'+@kskemu+',总分,总分年名,总分班名'
set @njbm='年名'
set @bjbm='班名'
select @tsql=isnull(@tsql+',','')
+'sum(case a.kemu when '''+kemu+''' then a.total else 0 end) '''+kemu+''', '
+'(select count(1) from
(select 1 ''d'' from cj b
where b.kemu='''+kemu+''' group by b.xuehao
having sum(b.total)>=(select sum(e.total) from cj e where e.xuehao=a.xuehao and e.kemu='''+kemu+''')) c) '+kemu+@njbm+', '
+'(select count(1) from
(select 1 ''d'' from cj b
where b.kemu='''+kemu+''' and b.classname=a.classname group by b.xuehao
having sum(b.total)>=(select sum(e.total) from cj e where e.xuehao=a.xuehao and e.kemu='''+kemu+''')) c) '+kemu+@bjbm+' '
from (select distinct kemu from cj) t
select @tsql='select a.ksid, a.classname ''班级'',a.xuehao ''学号'',a.stuname ''姓名'','+@tsql+','
+' sum(a.total) ''总分'',
(select count(1) from
(select 1 ''d'' from cj b group by b.xuehao having sum(b.total)>=(select sum(e.total) from cj e where e.xuehao=a.xuehao)) c) ''总分年名'',
(select count(1) from
(select 1 ''d'' from cj b where b.classname=a.classname group by b.xuehao having sum(b.total)>=(select sum(e.total) from cj e where e.xuehao=a.xuehao)) c) ''总分班名''
from cj a
where ksid= '+@conditions+'
group by a.xuehao,a.stuname,a.classname,a.ksid '
set @tsql='insert into cjdetail('+@kskemu+') ' + @tsql
exec(@tsql)
GO
set objrs=server.CreateObject("adodb.recordset")
objrs.open "myproc 1 ",objconn,1,1