---------------------------------
--  Author: liangCK 小梁
--  Date  : 2008-11-18 18:09:45
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (school VARCHAR(2),class VARCHAR(3),course VARCHAR(7),name VARCHAR(4),grade INT)
INSERT INTO @T
SELECT 'a1','001','math','mary',30 UNION ALL
SELECT 'a1','001','chinese','joel',67 UNION ALL
SELECT 'a1','003','math','than',78 UNION ALL
SELECT 'a1','004','ph','th',21 UNION ALL
SELECT 'b1','001','math','he',23 UNION ALL
SELECT 'b1','001','chinese','you',78

--SQL查询如下:

;WITH Liang
AS
(
    SELECT 
        *,
        RID1=ROW_NUMBER()
               OVER(PARTITION BY school 
                        ORDER BY GETDATE()),
        RID2=ROW_NUMBER() 
               OVER(PARTITION BY school,class
                        ORDER BY GETDATE())
    FROM @T
)
SELECT
   shcool=CASE WHEN RID1<>1 
                   THEN ''
                ELSE school
           END,
   class=CASE WHEN RID2<>1
                   THEN ''
                ELSE class
         END,
   course,
   name,
   grade
FROM Liang

/*
shcool class course  name grade
------ ----- ------- ---- -----------
a1     001   math    mary 30
             chinese joel 67
       003   math    than 78
       004   ph      th   21
b1     001   math    he   23
             chinese you  78

(6 行受影响)

*/

select case when exists (select 1 from [table] where school=a.school and (class<a.class or class=a.class and  course<a.course)) then '' else a.school end,
  case when exists (select 1 from [table] where school=a.school and class=a.class and  course<a.course) then '' else a.class end,
  a.course,  a.name,  a.grade 
from [table] a
order by a.school,a.class,a.course