select grade,case when (select count(*) from tab1 where grade=aa.grade and score='A')/(select count(*) from tab1 where grade=aa.grade)>=0.75 then 0 else 1 end as gscore from tab1 aa group by grade
调试欢乐多
case when not exists(select 1 from t where grade=a.grade and c_important=1 and score='B')
and (select sum(case when score='A' then 1 else 0 end)*1.0/sum(1) from t where grade=a.grade)>=0.75
then 1 else 0 end gscore
from t a group by grade
create table #t(grade char(2),class char(2),score char(1),c_important bit)
insert #t
select 'g1','c1','A',1
union select 'g1','c2','B',1
union select 'g2','c1','B',0
union select 'g2','c2','A',0
union select 'g2','c3','A',1
union select 'g2','c4','A',1select * from #t
goselect grade,
case when not exists(select 1 from #t where grade=a.grade and c_important=1 and score='B')
and (select sum(case when score='A' then 1 else 0 end)*1.0/sum(1) from #t where grade=a.grade)>=0.75
then 1 else 0 end gscore --into #tt
from #t a group by gradedrop table #t
(所影响的行数为 6 行)grade class score c_important
----- ----- ----- -----------
g1 c1 A 1
g1 c2 B 1
g2 c1 B 0
g2 c2 A 0
g2 c3 A 1
g2 c4 A 1(所影响的行数为 6 行)grade gscore
----- -----------
g1 0
g2 1(所影响的行数为 2 行)
create proc p_tab2
as
beginselect grade,
case when not exists(select 1 from tab1 where grade=a.grade and c_important=1 and score='B')
and (select sum(case when score='A' then 1 else 0 end)*1.0/sum(1) from tab1 where grade=a.grade)>=0.75
then 1 else 0 end gscore into tab2
from tab1 a group by gradeend
go