一个复杂查询问题,请高手来解答.谢谢
表中数据如下
希望用一条存储过程   取出aaa对于   1-1[In]   和   1-1[Out]分别的最大,最小时间
例如1-1[In]   的最大值是   '2007-03-22   19:14:16.000 '  
最小值是   '2007-03-22   14:59:34.000 '

2007-03-22   14:38:36.000 1-1[In] aaa
2007-03-22   14:38:38.000 1-1[In] aaa
2007-03-22   14:40:34.000 1-1[In] aaa
2007-03-22   14:42:20.000 1-1[In] aaa
2007-03-22   14:42:22.000 1-1[In] aaa
2007-03-22   14:42:26.000 1-1[In] aaa
2007-03-22   14:43:42.000 1-1[In] aaa
2007-03-22   14:51:02.000 1-1[In] aaa
2007-03-22   14:51:34.000 1-1[In] aaa
2007-03-22   14:52:04.000 1-1[In] aaa
2007-03-22   14:53:30.000 1-1[In] aaa
2007-03-22   14:58:16.000 1-1[In] aaa
2007-03-22   14:59:02.000 1-1[In] aaa
2007-03-22   14:59:04.000 1-1[In] aaa
2007-03-22   14:59:34.000 1-1[Exit] aaa
2007-03-22   14:59:36.000 1-1[Exit] aaa
2007-03-22   14:59:36.000 1-1[In] aaa
2007-03-22   15:39:44.000 1-1[Exit] aaa
2007-03-22   17:34:14.000 1-1[In] aaa
2007-03-22   17:34:16.000 1-1[Exit] aaa
2007-03-22   17:54:14.000 1-1[In] aaa
2007-03-22   17:54:34.000 1-1[In] aaa
2007-03-22   17:57:00.000 1-1[In] aaa
2007-03-22   17:57:40.000 1-1[Exit] aaa
2007-03-22   18:03:20.000 1-1[Exit] aaa
2007-03-22   18:03:24.000 1-1[In] aaa
2007-03-22   18:45:42.000 1-1[Exit] aaa
2007-03-22   18:46:08.000 1-1[In] aaa
2007-03-22   18:50:46.000 1-1[In] aaa
2007-03-22   18:50:48.000 1-1[In] aaa
2007-03-22   18:55:30.000 1-1[In] aaa
2007-03-22   18:55:32.000 1-1[In] aaa
2007-03-22   18:55:34.000 1-1[Exit] aaa
2007-03-22   18:55:34.000 1-1[In] aaa
2007-03-22   18:55:36.000 1-1[In] aaa
2007-03-22   18:55:38.000 1-1[In] aaa
2007-03-22   18:55:40.000 1-1[In] aaa
2007-03-22   18:55:42.000 1-1[In] aaa
2007-03-22   18:55:44.000 1-1[In] aaa
2007-03-22   19:14:12.000 1-1[In] aaa
2007-03-22   19:14:16.000 1-1[Exit] aaa

------解决方案--------------------
select aaa列
,min(case when 第二列 = '1-1[In] ' then 第一列 else null end) as IN的最小值
,max(case when 第二列 = '1-1[In] ' then 第一列 else null end) as IN的最大值
,min(case when 第二列 = '1-1[OUT] ' then 第一列 else null end) as OUT的最小值
,max(case when 第二列 = '1-1[OUT] ' then 第一列 else null end) as OUT的最小值
from t group by aaa列
------解决方案--------------------
create table T([date] datetime, col2 varchar(20), col3 varchar(20))
insert T select '2007-03-22 14:38:36.000 ', '1-1[In] ', 'aaa '
union all select '2007-03-22 14:38:38.000 ', '1-1[In] ', 'aaa '
union all select '2007-03-22 14:40:34.000 ', '1-1[In] ', 'aaa '
union all select '2007-03-22 14:42:20.000 ', '1-1[In] ', 'aaa '

select col2, min_date=min([date]), max_date=max([date])
from T
where col3= 'aaa '
group by col2