如数据源,
自增 操作员 操作时间 状态
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,在线等,搞定马上结,谢谢
自增 操作员 操作时间 状态
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 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')
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
from T
where 状态 in('B', 'D')
order by 自增 select #T1.操作员, #T1.操作说明, #T1.操作开始时间, #T2.操作结束时间
from #T1
left join #T2 on #T1.ID=#T2.ID and #T1.操作说明=#T2.操作说明
insert # 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 操作员, 操作时间 AS 操作开始时间,
(SELECT MIN(操作时间)
FROM # b
WHERE a.操作员=b.操作员
AND b.状态=CASE a.状态 WHEN 'A' THEN 'B' WHEN 'C' THEN 'D' END
AND b.操作时间>a.操作时间
AND b.自增>a.自增
AND
(SELECT COUNT(*)
FROM # c
WHERE a.状态=c.状态
AND c.自增<a.自增)
=
(SELECT COUNT(*)
FROM # d
WHERE b.状态=d.状态
AND d.自增<b.自增)) AS 操作结束时间,
CASE a.状态 WHEN 'A' THEN 'A-B' WHEN 'C' THEN 'C-D' END AS 操作说明
FROM # a
WHERE a.状态 IN ('A','C')操作员 操作开始时间 操作结束时间 操作说明
---------- ------------------------------------------------------ ------------------------------------------------------ ----
user1 2007-01-14 12:12:11.000 2007-01-14 12:12:29.000 A-B
user1 2007-01-14 12:12:13.000 2007-01-14 12:12:35.000 A-B
user1 2007-01-14 12:12:15.000 2007-01-14 12:12:36.000 A-B
user1 2007-01-14 12:14:11.000 2007-01-14 12:14:59.000 C-D
user1 2007-01-14 12:14:13.000 2007-01-14 12:15:35.000 C-D
user1 2007-01-14 12:14:55.000 2007-01-14 12:15:36.000 C-D(6 row(s) affected)
select aa.操作员 ,
aa.操作时间 ,
( select 操作时间 from t where 自增 = (select min(自增) from t where 状态 = case when aa.状态 = 'A' then 'B'
when aa.状态 = 'C' then 'D'
end
and 自增 > aa.自增 ) ) ,
aa.状态 + '-' + ( case when aa.状态 = 'A' then 'B'
when aa.状态 = 'C' then 'D'
end )
from (select * from t where 状态 in ('A' , 'C' ) ) aa