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 BT
SELECT '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          3
b          2
c          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]
go
create table [tb]([class] int,[student] varchar(1))
insert [tb]
select 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'
go

SELECT STUDENT,COUNT(DISTINCT CLASS) AS CNT 
FROM TB 
GROUP BY STUDENT

/**
STUDENT CNT
------- -----------
a       3
b       2
c       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的图标了，只能这样了