次数统计的SQL话语

次数统计的SQL语句
数据：
class, student
---------------
1,a
1,b
1,a
2,c
2,a
2,b
3,a
3,a
3,c


统计student在class中出现的次数，同个class多次出现，算1次，
结果应该为：
a,3
b,2
c,2

SQL咋写？

[解决办法]

SQL code

WITH test  AS (SELECT 1 class, 'a' student UNION ALL  SELECT 1,'b' UNION ALL  SELECT 1,'a' UNION ALL  SELECT 2,'c' UNION ALL  SELECT 2,'a' UNION ALL  SELECT 2,'b' UNION ALL  SELECT 3,'a' UNION ALL  SELECT 3,'a' UNION ALL  SELECT 3,'c') SELECT student,COUNT(1) [次数] FROM ( SELECT * FROM test GROUP BY class,student) a GROUP BY student /* student 次数 ------- ----------- a       3 b       2 c       2  (3 行受影响)  */
[解决办法]
SQL code
 CREATE TABLE  BT(    class varchar(10),    student varchar(10))INSERT INTO BTSELECT '1','a' UNION ALL SELECT '1','b' UNION ALL SELECT '1','a' UNION ALL SELECT '2','c' UNION ALL SELECT '2','a' UNION ALL SELECT '2','b' UNION ALL SELECT '3','a' UNION ALL SELECT '3','a' UNION ALL SELECT '3','c'select T.student,Count(T.student) CountStudent from (select Distinct class,student from BT) T group by T.student/*student    CountStudent---------- ------------a          3b          2c          2(3 行受影响)*/
[解决办法]
SQL code
 SELECT student,COUNT(*)  FROM (SELECT * FROM tb GROUP BY class,student) t GROUP BY student
[解决办法]
SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([class] int,[student] varchar(1))insert [tb]select 1,'a' union allselect 1,'b' union allselect 1,'a' union allselect 2,'c' union allselect 2,'a' union allselect 2,'b' union allselect 3,'a' union allselect 3,'a' union allselect 3,'c'goSELECT STUDENT,COUNT(DISTINCT CLASS) AS CNT FROM TB GROUP BY STUDENT/**STUDENT CNT------- -----------a       3b       2c       2(3 行受影响)**/
[解决办法]
WITH test  
  AS (
SELECT 1 class, 'a' student
  UNION ALL  
  SELECT 1,'b'
  UNION ALL  
  SELECT 1,'a'
  UNION ALL  
  SELECT 2,'c'
  UNION ALL  
  SELECT 2,'a'
  UNION ALL  
  SELECT 2,'b'
  UNION ALL  
  SELECT 3,'a'
  UNION ALL  
  SELECT 3,'a'
  UNION ALL  
  SELECT 3,'c')
,c1 AS
(
SELECT DISTINCT class,student  --去掉重复记录
FROM test
)
SELECT student, COUNT(student) 次数
FROM c1
GROUP BY c1.student

---看不见SQL code的图标了，只能这样了