急求统计SQL
如数据源，
自增 操作员       操作时间                         状态
2 user1 2007-1-14   12:12:11     A
3 user1 2007-1-14   12:12:29     B
4 user1 2007-1-14   12:12:13     A
5 user1 2007-1-14   12:12:15     A
6 user1 2007-1-14   12:12:35     B
7 user1 2007-1-14   12:12:36     B
8 user1 2007-1-14   12:14:11     C
9 user1 2007-1-14   12:14:59     D
10 user1 2007-1-14   12:14:13     C
11 user1 2007-1-14   12:14:55     C
12 user1 2007-1-14   12:15:35     D
13 user1 2007-1-14   12:15:36     D

A-B操作为第一种状态
C-D操作为第二种状态

实现统计效果如下
操作员 操作开始时间                 操作结束时间   操作说明      
user1       2007-1-14   12:12:11   2007-1-14   12:12:29 A-B
user1 2007-1-14   12:12:13   2007-1-14   12:12:35 A-B
user1 2007-1-14   12:12:15   2007-1-14   12:12:36 A-B
user1 2007-1-14   12:14:11   2007-1-14   12:14:59 C-D
user1 2007-1-14   12:14:13   2007-1-14   12:15:35 C-D
user1 2007-1-14   12:14:55   2007-1-14   12:15:36 C-D

急求统计SQL，在线等，搞定马上结，谢谢




------解决方案--------------------
SELECT 操作员, 操作时间 AS 操作开始时间,
(SELECT MIN(操作时间)
FROM table1 b
WHERE a.操作员=b.操作员
AND b.状态=CASE a.状态 WHEN 'A ' THEN 'B ' WHEN 'C ' THEN 'D ' END
AND b.操作时间> a.操作时间) AS 操作结束时间,
CASE a.状态 WHEN 'A ' THEN 'A-B ' WHEN 'C ' THEN 'C-D ' END AS 操作说明
FROM table1 a
WHERE a.状态 IN ( 'A ', 'C ')
------解决方案--------------------
create table T(自增 int, 操作员 varchar(10), 操作时间 datetime, 状态 varchar(10))
insert T select 2, 'user1 ', '2007-1-14 12:12:11 ', 'A '
union all select 3, 'user1 ', '2007-1-14 12:12:29 ', 'B '
union all select 4, 'user1 ', '2007-1-14 12:12:13 ', 'A '
union all select 5, 'user1 ', '2007-1-14 12:12:15 ', 'A '
union all select 6, 'user1 ', '2007-1-14 12:12:35 ', 'B '
union all select 7, 'user1 ', '2007-1-14 12:12:36 ', 'B '
union all select 8, 'user1 ', '2007-1-14 12:14:11 ', 'C '
union all select 9, 'user1 ', '2007-1-14 12:14:59 ', 'D '
union all select 10, 'user1 ', '2007-1-14 12:14:13 ', 'C '
union all select 11, 'user1 ', '2007-1-14 12:14:55 ', 'C '
union all select 12, 'user1 ', '2007-1-14 12:15:35 ', 'D '
union all select 13, 'user1 ', '2007-1-14 12:15:36 ', 'D '


select
ID=identity(int, 1, 1),
操作员,
操作说明=case when 状态 in( 'A ', 'B ') then '[A-B] ' when 状态 in( 'C ', 'D ') then '[C-D] ' end,
操作开始时间=操作时间
into #T1
from T
where 状态 in( 'A ', 'C ')
order by 自增

select
ID=identity(int, 1, 1),
操作说明=case when 状态 in( 'A ', 'B ') then '[A-B] ' when 状态 in( 'C ', 'D ') then '[C-D] ' end,
操作结束时间=操作时间 into #T2