Role Class Count(Role)Barbie 2 1
Barbie 3 3
Barbie ALL 4
Hello Kitty 1 3
Hello Kitty 2 8
Hello Kitty 3 8
Hello Kitty 4 9
Hello Kitty 5 8
Hello Kitty ALL 36
Barbie 3 3
Barbie ALL 4
Hello Kitty 1 3
Hello Kitty 2 8
Hello Kitty 3 8
Hello Kitty 4 9
Hello Kitty 5 8
Hello Kitty ALL 36
Role Class Count(Role)Hello Kitty 1 3
Hello Kitty 2 8
Hello Kitty 3 8
Hello Kitty 4 9
Hello Kitty 5 8
Hello Kitty ALL 36
Barbie 2 1
Barbie 3 3
Barbie ALL 4
改成
ORDER BY Role DESC,Class
SELECT
CASE WHEN (GROUPING(Role) = 1) THEN 'ALL'
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
CASE WHEN (GROUPING(Role) = 1) THEN 'ALL'
ELSE ISNULL(Role, 'UNKNOWN')
END AS Role,
CASE WHEN (GROUPING(Class) = 1) THEN 'ALL'
ELSE ISNULL(Class, 'UNKNOWN')
END AS Class,
COUNT(Role) rolenum
FROM Vote_student WHERE Class BETWEEN 1 AND 6 ) tem GROUP BY Role,Class WITH CUBE
ORDER BY Role,Class
group by class, role
order by count(*)
SELECT CASE WHEN (GROUPING(Role) = 1) THEN 'ALL'
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
GROUP BY Role,Class WITH CUBE
ORDER BY COUNT(Role),Role,Class
我那條語句得到的結果是:http://www.pcclub.com.hk/a.htm
而我想得到的結果是:http://www.pcclub.com.hk/b.htm
group by class, role
order by count(*)
我是想對得到的每個role在所有Class中的總數來對Role進行排序:
select
orderby count(Role) desc可
表結構在:http://www.pcclub.com.hk/db1.mdb
我那條語句得到的結果是:http://www.pcclub.com.hk/a.htm
而我想得到的結果是:http://www.pcclub.com.hk/b.htm
有這三個文件都應該知道我想實現的東東吧。
請大家看清楚,在求每個 Role 的數量的時候,分別是對每個Class求總 和 對所有 Class求總。 我是要按照所有Class的Role數量來對Role來排序。
我想這個並不是很簡單就能實現的,並不是一句話就能解決的,不然,我也不會到這裡來請教大家了。
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