set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[fsd]
@km as varchar(max)
AS
BEGIN
    drop table zffsd
    exec zf @km
    declare @s varchar(max),@ss varchar(max)
    declare @i int,@j int,@top int,@bottom int,@from int,@to int
    declare @bj int
    set @s=''
set @ss=''
    set @top=700
    set @bottom=200
    set @j=@top

        declare my_cursor cursor scroll dynamic
        for
        select distinct [班级] from  mcb
        open my_cursor
        
        fetch next from my_cursor into @bj
        set @s='select '''+ltrim(str(@top))+'以上'''+' 段次'
        while(@@fetch_status=0)
        begin
            --print 'Query ID: ' + cast(@bj as varchar)
            set @s=@s+',(select count(*) from mcb where 班级='+ltrim(str(@bj))+' and 总分>='+ltrim(str(@top))+') 计'+ltrim(str(@bj))+',(select count(*) from mcb where 班级='+ltrim(str(@bj))+' and 总分>='+ltrim(str(@top))+') 累'+ltrim(str(@bj))
            fetch next from my_cursor into @bj
        end
        set @s=@s+',(select count(*) from mcb where 总分>='+ltrim(str(@top))+') 总计'+',(select count(*) from mcb where 总分>='+ltrim(str(@top))+') 总累 into zffsd'
--print @s
        --select @s uuu
        --exec(@s)
set @ss=@ss+@s+' union '
        close my_cursor
        deallocate my_cursor

    while @j>=@bottom+10
    begin
        set @from=@j-10
        set @to=@j

        declare my_cursor cursor scroll dynamic
        for
        select distinct [班级] from  mcb
        open my_cursor
        
        fetch next from my_cursor into @bj
        set @s='select '''+ltrim(str(@j-10))+''' 段次'
        while(@@fetch_status=0)
        begin
            --print 'Query ID: ' + cast(@bj as varchar)
            set @s=@s+',(select count(*) from mcb where 班级='+ltrim(str(@bj))+' and 总分>='+ltrim(str(@from))+' and 总分<'+ltrim(str(@to))+') 计'+ltrim(str(@bj))+',(select count(*) from mcb where 班级='+ltrim(str(@bj))+' and 总分>='+ltrim(str(@from))+') 累'+ltrim(str(@bj))
            fetch next from my_cursor into @bj
        end
        set @s=@s+',(select count(*) from mcb where 总分>='+ltrim(str(@from))+' and 总分<'+ltrim(str(@to))+') 总计'+',(select count(*) from mcb where 总分>='+ltrim(str(@from))+') 总累'
        --select @s
        --exec(@s)
set @ss=@ss+@s+' union '
        close my_cursor
        deallocate my_cursor

        set @j=@j-10
    end

        set @from=@top
        declare my_cursor cursor scroll dynamic
        for
        select distinct [班级] from  mcb
        open my_cursor
        
        fetch next from my_cursor into @bj
        set @s='select '' '+ltrim(str(@bottom))+'以下'''+' 段次'
        while(@@fetch_status=0)
        begin
            --print 'Query ID: ' + cast(@bj as varchar)
            set @s=@s+',(select count(*) from mcb where 班级='+ltrim(str(@bj))+' and 总分>0 and 总分<'+ltrim(str(@bottom))+') 计'+ltrim(str(@bj))+',(select count(*) from mcb where 班级='+ltrim(str(@bj))+' and 总分>0) 累'+ltrim(str(@bj))
            fetch next from my_cursor into @bj
        end
        set @s=@s+',(select count(*) from mcb where 总分>0 and 总分<'+ltrim(str(@bottom))+') 总计'+',(select count(*) from mcb where 总分>0) 总累'
        --select @s uuu
        --exec(@s)
set @ss=@ss+@s+' order by 段次 desc'
        close my_cursor
        deallocate my_cursor
END
select @ss
--print @ss
exec(@ss)