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
--tryselect 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_idwhere a.f_grade_id = 1