DROP TABLE tempdb..#t
SELECT * INTO #t
FROM
(
SELECT
N'070101101' AS [学号] ,N'张三' AS [姓名],N'20072' AS [学年],N'C语言程序设计' AS[课程名], N'正常考试' AS[考试方式],
N'52' AS [成绩] , N'考试' AS [考核方式]
UNION ALL SELECT
N'070101101',N'张三', N'20072',N'C语言程序设计',N'补考1' , N'及格' , N''
UNION ALL SELECT
N'070101101',N'张三',N'20071' , N'大学英语1' ,N'正常考试' , N'60' ,
N'考试'
UNION ALL SELECT
N'070101101',N'张三',N'20072' , N'大学英语2',
N'正常考试' ,N'48' ,
N'考试'
UNION ALL SELECT
N'070101101' , N'张三',N'20072' , N'大学英语2', N'补考1' , N'及格',
N''
UNION ALL SELECT
N'070101101', N'张三',N'20071',N'体育',
N'正常考试',N'不及格', N'考查'
UNION ALL SELECT
N'070101101', N'张三',N'20071', N'体育',
N'补考1',
N'及格',
N''
UNION ALL SELECT
N'070101101', N'张三',N'20071',N'素描',
N'正常考试',N'0', N'考查'
UNION ALL SELECT
N'070101101', N'张三',N'20071', N'素描',
N'补考1',
N'及格',
N''
UNION ALL SELECT
N'070101101', N'张三',N'20072',N'数据库',
N'正常考试',NULL, N'考试'
UNION ALL SELECT
N'070101101', N'张三',N'20072', N'数据库',
N'补考1',
N'不及格',
N''
) AS T
SELECT * FROM #t
--
SELECT
[学号], [姓名], [学年], [课程名], MIN(ISNULL([成绩],N'0')) AS [成绩],
MAX([考核方式]) AS [考核方式],
CASE WHENISNUMERIC( MAX(ISNULL([成绩],N'0')) ) = 0 THEN MAX(ISNULL([成绩],N'0'))ELSE '' END AS [补考1]
FROM #t
GROUP BY [学号], [姓名], [学年], [课程名]
|