select role, class, count(role) AS co into #temp from vote_student group by role,class
insert into #temp select role, null, count(role) AS co from vote_student group by role
select * from #temp order by role, class
insert into #temp select role, null, count(role) AS co from vote_student group by role
select * from #temp order by role, class
我那條語句得到的結果是:http://www.pcclub.com.hk/a.htm
而我想得到的結果是:http://www.pcclub.com.hk/b.htm大家可以打開看啊!!
insert into #temp select role + ' All', null, count(role) AS co from vote_student group by role
select * from #temp order by role, class
ELSE ISNULL(Role, 'UNKNOWN')
END AS Role,
CASE WHEN (GROUPING(Class) = 1) THEN 'ALL'
ELSE ISNULL(Class, 'UNKNOWN')
END AS Class,
COUNT(Role)
FROM Vote_student
WHERE Class BETWEEN 1 AND 6 ) tem
GROUP BY Role,Class WITH CUBE
ORDER BY Role,Class
select role, class, count(role) AS co into #temp from vote_student group by role,class
insert into #temp select role, null, count(role) AS co from vote_student group by role
select role, count(role) AS co into #temp1 from vote_student group by role
select a.role,a.class,a.co from #temp as a,#temp1 as b where a.role=b.role order by b.co desc,a.class
go