请教语句
有如下表a，
班别 姓名 成绩
初一1 a 50
初一1 b 80
初一2 c 60
初二1 d 20


可以查询统计出各班成绩>60的人数建立如下表格吗？？谢谢
年级\班别 1 2 合计
  初一 1 1 2
  初二 0 0 0
  初三 0 0 0
按照初一，初二，初三排列，如果为0的也要显示出来啊。谢谢


------解决方案--------------------
TRANSFORM SUM(IIF(成绩>=60,1,0))
SELECT LEFT(班别,2),SUM(IIF(成绩>=60,1,0)) FROM (
SELECT *,RIGHT(班别,1) AS BB FROM A
UNION
SELECT DISTINCT RIGHT('初一1',1),'初一1','',0 FROM A
UNION
SELECT DISTINCT RIGHT('初二1',1),'初二1','',0 FROM A
UNION
SELECT DISTINCT RIGHT('初三1',1), '初三1','',0 FROM A)
GROUP BY LEFT(班别,2)
PIVOT BB
------解决方案--------------------

SQL code

select gid,
    sum(iif(cid='1',1,0)) as [1],
    sum(iif(cid='2',1,0)) as [2],
    sum(iif(cid='1',1,0))+sum(iif(cid='2',1,0)) as 合计
FROM (
select left(班别,2) as gid,mid(班别,3) as cid from a where val(成绩)>=60
union all
select top 1 '初一','0' from a
union all               
select top 1 '初二','0' from a
union all               
select top 1 '初三','0' from a
) t
group by gid
order by instr('初一,初二,初三',gid)