一个复杂查询问题,请高手来解答.谢谢
表中数据如下
希望用一条存储过程 取出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.0001-1[In]aaa
2007-03-22 14:38:38.0001-1[In]aaa
2007-03-22 14:40:34.0001-1[In]aaa
2007-03-22 14:42:20.0001-1[In]aaa
2007-03-22 14:42:22.0001-1[In]aaa
2007-03-22 14:42:26.0001-1[In]aaa
2007-03-22 14:43:42.0001-1[In]aaa
2007-03-22 14:51:02.0001-1[In]aaa
2007-03-22 14:51:34.0001-1[In]aaa
2007-03-22 14:52:04.0001-1[In]aaa
2007-03-22 14:53:30.0001-1[In]aaa
2007-03-22 14:58:16.0001-1[In]aaa
2007-03-22 14:59:02.0001-1[In]aaa
2007-03-22 14:59:04.0001-1[In]aaa
2007-03-22 14:59:34.0001-1[Exit]aaa
2007-03-22 14:59:36.0001-1[Exit]aaa
2007-03-22 14:59:36.0001-1[In]aaa
2007-03-22 15:39:44.0001-1[Exit]aaa
2007-03-22 17:34:14.0001-1[In]aaa
2007-03-22 17:34:16.0001-1[Exit]aaa
2007-03-22 17:54:14.0001-1[In]aaa
2007-03-22 17:54:34.0001-1[In]aaa
2007-03-22 17:57:00.0001-1[In]aaa
2007-03-22 17:57:40.0001-1[Exit]aaa
2007-03-22 18:03:20.0001-1[Exit]aaa
2007-03-22 18:03:24.0001-1[In]aaa
2007-03-22 18:45:42.0001-1[Exit]aaa
2007-03-22 18:46:08.0001-1[In]aaa
2007-03-22 18:50:46.0001-1[In]aaa
2007-03-22 18:50:48.0001-1[In]aaa
2007-03-22 18:55:30.0001-1[In]aaa
2007-03-22 18:55:32.0001-1[In]aaa
2007-03-22 18:55:34.0001-1[Exit]aaa
2007-03-22 18:55:34.0001-1[In]aaa
2007-03-22 18:55:36.0001-1[In]aaa
2007-03-22 18:55:38.0001-1[In]aaa
2007-03-22 18:55:40.0001-1[In]aaa
2007-03-22 18:55:42.0001-1[In]aaa
2007-03-22 18:55:44.0001-1[In]aaa
2007-03-22 19:14:12.0001-1[In]aaa
2007-03-22 19:14:16.0001-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
