求助各位,这样的语句如何简化
SELECT COUNT(id) AS classcount, sum(money) as zmoney
  (SELECT COUNT(id) AS classcount 
  FROM dbo.class 
  WHERE (f_grade_id = 1) AND type = 1) AS ptb, 
  (SELECT sum(money) AS classmoney
  FROM dbo.class 
  WHERE (f_grade_id = 1) AND type = 1) AS ptbmoney,
  (SELECT COUNT(id) AS classcount 
  FROM dbo.class 
  WHERE (f_grade_id = 1) AND type = 2) AS cwb, 
  (SELECT sum(money) AS classmoney
  FROM dbo.class 
  WHERE (f_grade_id = 1) AND type = 2) AS cwbmoney,
  (SELECT COUNT(id) AS classcount 
  FROM dbo.class 
  WHERE (f_grade_id = 1) AND type = 3) AS clb, 
  (SELECT sum(money) AS classmoney
  FROM dbo.class 
  WHERE (f_grade_id = 1) AND type = 3) AS clbmoney,
  (SELECT COUNT(id) AS classcount 
  FROM dbo.class 
  WHERE (f_grade_id = 1) AND type = 4) AS tsb,
  (SELECT sum(money) AS classmoney
  FROM dbo.class 
  WHERE (f_grade_id = 1) AND type = 4) AS tsbmoney 

FROM dbo.class 
WHERE (f_grade_id = 1)

------解决方案--------------------

SQL code

--try

select 
    count([id]) AS classcount, 
    sum([money]) as zmoney,
    sum(case when b.type = 1 then b.cnt else 0 end) AS ptb, 
    sum(case when b.type = 1 then classmoney else 0 end) AS ptbmoney, 
    sum(case when b.type = 2 then b.cnt else 0 end) AS cwb, 
    sum(case when b.type = 2 then classmoney else 0 end) AS cwbmoney, 
    sum(case when b.type = 3 then b.cnt else 0 end) AS clb, 
    sum(case when b.type = 3 then classmoney else 0 end) AS clbmoney, 
    sum(case when b.type = 4 then b.cnt else 0 end) AS tsb, 
    sum(case when b.type = 4 then classmoney else 0 end) AS tsbmoney 
from dbo.class  as a
    left join 
    (
        select f_grade_id,type,count(*) as cnt, sum([money]) AS classmoney 
        from dbo.class 
        where (f_grade_id = 1)
        group by f_grade_id,type
    ) as b on a.f_grade_id=b.f_grade_id
where a.f_grade_id = 1