原贴：
这是一个证书考试记录系统，每个学生都要通过考试获得证书，每个证书都有失效日期。
1,如果学生目前所有证书(3个)都在有效期内（当前系统时间）则显示状态为’正常‘；
2,如果有证书失效，但是此学生参加了新的课程（且早于当前系统时间），则状态显示为'正常'；
3，如果有证书失效，且没有新课程，则显示学生状态'失效'.
当前系统时间 1/21/2010
数据集：
学生ID 学生姓名 证书名称 上次考证时间 失效日期 新课时间
SID Sname Qulification LastDate DueDate NewClass
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
0342S aa CCDF 1/23/2010 1/19/2010 2/1/2010
0748T bb RTDF 1/22/2010 5/22/2010 2/1/2010
0748T bb CCDF Null 5/22/2010 2/1/2010
0748T bb FDO Null 5/22/2010 2/1/2010
55576 cc CCDF 4/22/2008 4/29/2010 2/1/2010
55576 cc FDO 4/22/2008 4/29/2010 2/1/2010
55576 cc RTDF 4/22/2008 4/29/2010 2/1/2010
64221 dd RTDF 1/20/2010 5/22/2010 2/1/2010
希望输出结果
学生ID 姓名 RTDF上次 RTDF失效 CCDF上次 CCDF失效 FDO上次 FDO失效 状态
SID Sname RTDF_Comp RTDF_Due CCDF_Completed CCDF_Due FDO_Completed FDO_Due Status
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
0342S aa No Class No Class 1/23/2010 1/19/2010 No Class No Class Expire
55576 cc 4/22/2008 4/29/2010 4/22/2008 4/29/2010 4/22/2008 4/29/2010 Current
0748T bb 1/22/2010 5/22/2010 New Class 5/22/2010 New Class 5/22/2010 Current
64221 dd 1/20/2010 5/22/2010 No Class No Class No Class No Class Expire
代码处理过程：
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-01 07:19:04
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([SID] NVARCHAR(10),[Sname] NVARCHAR(10),[Qulification] NVARCHAR(10),[LastDate] DATETIME,[DueDate] DATETIME,[NewClass] DATETIME)
INSERT [tb]
SELECT '0342S','aa','CCDF',N'1/23/2010',N'1/19/2010',N'2/1/2010' UNION ALL
SELECT '0748T','bb','RTDF',N'1/22/2010',N'5/22/2010',N'2/1/2010' UNION ALL
SELECT '0748T','bb','CCDF',NULL,N'5/22/2010',N'2/1/2010' UNION ALL
SELECT '0748T','bb','FDO',NULL,N'5/22/2010',N'2/1/2010' UNION ALL
SELECT '55576','cc','CCDF',N'4/22/2008',N'4/29/2010',N'2/1/2010' UNION ALL
SELECT '55576','cc','FDO',N'4/22/2008',N'4/29/2010',N'2/1/2010' UNION ALL
SELECT '55576','cc','RTDF',N'4/22/2008',N'4/29/2010',N'2/1/2010' UNION ALL
SELECT '64221','dd','RTDF',N'1/20/2010',N'5/22/2010',N'2/1/2010'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
DECLARE @S VARCHAR(8000)
SELECT @S='SELECT SID,[Sname]'
SELECT @S=@S+',MIN(CASE WHEN [Qulification]='''+[Qulification]+''' AND [NewClass] IS NULL THEN ISNULL(CONVERT(VARCHAR,[LastDate],111),''NO CLASS'')
WHEN [Qulification]='''+[Qulification]+''' AND [NewClass] IS NOT NULL THEN ISNULL(CONVERT(VARCHAR,[LastDate],111),''NEW CLASS'')
ELSE ''No Class'' END)['+[Qulification]+'上次]'+
+',MIN(CASE WHEN [Qulification]='''+[Qulification]+''' THEN CONVERT(VARCHAR,[DueDate],111) ELSE ''No Class'' END)['+[Qulification]+'失效]'
FROM (SELECT [QULIFICATION] FROM TB GROUP BY CHARINDEX(LEFT([QULIFICATION],1),'RCF'),[QULIFICATION]) T
SELECT @S=@S+',Status=CASE SUM(CASE WHEN [DueDate]>GETDATE() OR ([DueDate]<GETDATE() AND ISNULL([NewClass],0)>GETDATE()) THEN 1 ELSE 0 END) WHEN 3 THEN ''Current'' ELSE ''Expire'' END
FROM TB --WHERE Sname<>''AA''
GROUP BY SID,[Sname]'
EXEC(@S)
/*
SID Sname RTDF上次 RTDF失效 CCDF上次 CCDF失效 FDO上次 FDO失效 Status
-------- ------- ---------- ---------- ---------- ---------- ---------- ------------- -----------
0342S aa No Class No Class 2010/01/23 2010/01/19 No Class No Class Expire
0748T bb 2010/01/22 2010/05/22 NEW CLASS 2010/05/22 NEW CLASS 2010/05/22 Current
55576 cc 2008/04/22 2010/04/29 2008/04/22 2010/04/29 2008/04/22 2010/04/29 Current
64221 dd 2010/01/20 2010/05/22 No Class No Class No Class No Class Expire
(4 行受影响)
*/