4_5(2).sql

来自「to become acquainted with a concept &#17」· SQL 代码 · 共 57 行

if (cursor_status('global', 'cursor1') <> -3) 
	deallocate global cursor1

declare cursor1 cursor
global
scroll
static
for select  grouppa.id_group
  from Grouppa 
  inner JOIN Student 
  ON grouppa.id_group=Student.id_group
  inner join form_edu
  on form_edu.id_form=student.id_form
  where form_edu.form_name='Vechernee' or form_edu.form_name= 'Zaochne'
	ORDER BY 	id_group	




open cursor1
declare 
@kol int,
@id1 int,
@id2 int,
@idmax int,
@max int
	set @kol=0
	set @id1=0
	set @id2=0
    set @idmax=0
	set @max=0
		
while(1=1)
begin

	fetch next from cursor1 into @id1
	--SELECT @ID1
			if(@@fetch_status=-1)
				break;
			if(@@fetch_status<>-2)
			begin
				if (@id1=@id2) 		set @kol=@kol+1
			    if (@kol>@max) 
				begin
				  set @max=@kol
				  set @idmax=@id2	
				end
				if (@id1<>@id2) set @kol=0
				set @id2=@id1
			end
		
end

close cursor1
deallocate global cursor1

select @idmax as idmax, @max as kol