---------------------------------
-- 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 行受影响)*/
-- 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 行受影响)*/
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