求这样一个SQL语句
表a结构
Name BigClass SmallClass
张三 1 101
李四 1 102
王五 2 201
表b结构
smallClass time1 time2
101 2012-10-19 2012-10-19
102 2012-10-19
201 2012-10-19
101 2012-10-19
101 2012-10-19

要求结果
姓名 TIME1和TIME2都存在的记录数 只有TIME1的记录数 只有TIME2的记录数


[解决办法]
try

SQL code

--> 测试数据：[ta]IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]GO CREATE TABLE [ta]([Name] VARCHAR(4),[BigClass] INT,[SmallClass] INT)INSERT [ta]SELECT '张三',1,101 UNION ALLSELECT '李四',1,102 UNION ALLSELECT '王五',2,201--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([smallClass] INT,[time1] DATETIME,[time2] DATETIME)INSERT [tb]SELECT 101,'2012-10-19','2012-10-19' UNION ALLSELECT 102,'2012-10-19',NULL UNION ALLSELECT 201,'2012-10-19',NULL UNION ALLSELECT 101,'2012-10-19',NULL UNION ALLSELECT 101,'2012-10-19',NULL--------------开始查询--------------------------SELECT [Name],SUM (case WHEN [time1] is NOT NULL AND [time2] is NOT NULL THEN 1 ELSE 0 END) AS [time1 time2 都不为null],SUM (case WHEN [time1] is NOT NULL AND [time2] is  NULL THEN 1 ELSE 0 END) AS [time2 为null],SUM (case WHEN [time1] is  NULL AND [time2] is NOT NULL THEN 1 ELSE 0 END) AS [time1 为null]FROM [ta] AS a JOIN  [tb] AS b  ON a.[smallClass]=b.[smallClass]GROUP BY [Name]----------------结果----------------------------/* Name    time1 time2 都不为null    time2 为null    time1 为null李四    0    1    0王五    0    1    0张三    1    2    0*/