我有一个表 USERNAME varchar(10) NumSchool int NumStudent int, 数据为 USERNAME NumSchool NumStu

A1:--------------------------------------------------------------------------
select USERNAME,
(select sum(NumSchool) from table as a where a.USERNAME=table.USERNAME) as NumSchoolAll,
(select sum(NumStudent) from table as a where a.USERNAME=table.USERNAME) as NumStudentAll,
(select sum(NumSchool) from table as a where a.USERNAME=table.USERNAME and numschool>0 ) as numschool_0,
(select sum(NumStudent) from table as a where a.USERNAME=table.USERNAME and numschool>0 ) as NumStudent
from table

A2---------------------------------------------------------------

select USERNAME, sum(NumSchool) as NumSchoolAll, sum(NumStudent) as NumStudentAll,
(select sum(NumSchool) from table as a where a.USERNAME=table.USERNAME and numschool>0 ) as 'numschool>0',
(select sum(NumStudent) from table as a where a.USERNAME=table.USERNAME and numschool>0 ) as NumStudent
from table1 group by USERNAME

A3-----------------------------------------------------------------------
select USERNAME, sum(NumSchool) as NumSchoolAll, sum(NumStudent) as NumStudentAll,
sum(case when numschool>0 then NumSchool else 0 end) as 'numschool>0',
sum(case when numschool>0 then NumStudent else 0 end) as NumStudent
from #t group by USERNAME
A4---------------------------------------------------------------
select USERNAME, sum(NumSchool) as NumSchoolAll, sum(NumStudent) as NumStudentAll,sum(NumStudent) as NumStudent>0 ,
sum(case when numschool>0 then NumStudent else 0 end) as NumStudent
from table group by USERNAME