select a.id_alu as CODIGO,ape_per as APELLIDO,nom_per as NOMBRE,des_cur as SECCION 
,des_mat as ASIGNATURA,nota as NOTA ,nota_r as RECUPERACION,nota_d NOTA_DEFINITIVA
from
(select id_alu,cod_cur  ,cod_mat, nota_r,nota,nota_d
from(
select id_alu,cod_cur ,cod_mat, nota, nota_r , nota_d,nota_d nd from seg_final s ,periodo p
where s.id_peri = p.id_peri and per_not_fin='s'
) a0)a 
inner join(
	select * from (
		select a0.id_alu ,count(nota) as TOTAL from 
		(select id_alu,cod_cur  ,cod_mat,if((nota_d-truncate(nota_d,1))>=0.05 ,round(ceil(nota*10)/10,1),round(nota_d,1)) as nota  
		from seg_final
		where id_peri = (select id_peri from periodo  where per_not_fin ='s' and per_fin='n') and cod_mat<>0
		) a0 
		inner  join rangos_valora r on nota>=min and nota<=max
		inner join tipvalora v on v.id_val=r.id_val and v.val_apr='n'
		inner join alumno l on l.id_alu=a0.id_alu
		where est_alu='h'
		group by id_alu,cod_cur 
	) xxxx 
	where  TOTAL <= VAR02
) xxx on  xxx.id_alu=a.id_alu
inner  join rangos_valora r on nota_d>=min and nota_d<=max
inner join tipvalora v on v.id_val=r.id_val   and (v.val_apr='n'  )
inner join materia t using(cod_mat)
inner join alumno l on l.id_alu=a.id_alu
inner join curso c using (cod_cur)
inner join grado g using (cod_gra)
where est_alu='h' and cod_gra = VAR01 
order by cod_cur,ape_per,t.cod_mat