if object_id('test') is not null drop table test
create table test
(
ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(50),
TIME datetime
)
insert test
select 'StartCall', '2010-06-13 16:45:25' union all
select 'Attempt', '2010-06-13 16:45:28' UNION ALL
select 'Connect', '2010-06-13 16:45:50' UNION ALL
select 'CallEnd', '2010-06-13 16:46:05' UNION ALL
select 'StartCall', '2010-06-13 16:55:25' union all
select 'Attempt', '2010-06-13 16:55:28' UNION ALL
select 'Connect', '2010-06-13 16:55:50' UNION ALL
select 'CallEnd', '2010-06-13 16:56:05' UNION ALL
select 'StartCall', '2010-06-13 16:57:25' union all
select 'Attempt', '2010-06-13 16:57:28' UNION ALL
select 'Connect', '2010-06-13 16:57:50' UNION ALL
select 'CallEnd', '2010-06-13 16:57:05' UNION ALL
select 'StartCall', '2010-06-13 16:58:25' UNION ALL
select 'Attempt', '2010-06-13 16:58:25' UNION ALL
select 'Connect', '2010-06-13 16:58:25' SELECT * FROM test
--拨打流程
--StartCall—— Attempt —— Connect —— CallEnd,有多少个callend说明有多少次完整的呼叫
SELECT * FROM Test WHERE NAME = 'CallEnd' --3 行记录 说明 3次完整的呼叫
--现在要显示整个流程,先确定什么时候Callend ,然后向上反过来找到(Connect—— Attempt —— StartCall)的时间
if object_id('test2') is not null drop table test2
create table test2
(
ID INT IDENTITY(1,1) PRIMARY KEY,
StartCall DATETIME,
Attempt DATETIME,
Connect DATETIME,
Callend DATETIME
)
--最后展示的结构如下(当然这里的test2您能帮我做成动态表更好) ,对于不足一个流程的数据就舍弃掉
SELECT * FROM test2 --这里面就该有3条记录
create table test
(
ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(50),
TIME datetime
)
insert test
select 'StartCall', '2010-06-13 16:45:25' union all
select 'Attempt', '2010-06-13 16:45:28' UNION ALL
select 'Connect', '2010-06-13 16:45:50' UNION ALL
select 'CallEnd', '2010-06-13 16:46:05' UNION ALL
select 'StartCall', '2010-06-13 16:55:25' union all
select 'Attempt', '2010-06-13 16:55:28' UNION ALL
select 'Connect', '2010-06-13 16:55:50' UNION ALL
select 'CallEnd', '2010-06-13 16:56:05' UNION ALL
select 'StartCall', '2010-06-13 16:57:25' union all
select 'Attempt', '2010-06-13 16:57:28' UNION ALL
select 'Connect', '2010-06-13 16:57:50' UNION ALL
select 'CallEnd', '2010-06-13 16:57:05' UNION ALL
select 'StartCall', '2010-06-13 16:58:25' UNION ALL
select 'Attempt', '2010-06-13 16:58:25' UNION ALL
select 'Connect', '2010-06-13 16:58:25' SELECT * FROM test
--拨打流程
--StartCall—— Attempt —— Connect —— CallEnd,有多少个callend说明有多少次完整的呼叫
SELECT * FROM Test WHERE NAME = 'CallEnd' --3 行记录 说明 3次完整的呼叫
--现在要显示整个流程,先确定什么时候Callend ,然后向上反过来找到(Connect—— Attempt —— StartCall)的时间
if object_id('test2') is not null drop table test2
create table test2
(
ID INT IDENTITY(1,1) PRIMARY KEY,
StartCall DATETIME,
Attempt DATETIME,
Connect DATETIME,
Callend DATETIME
)
--最后展示的结构如下(当然这里的test2您能帮我做成动态表更好) ,对于不足一个流程的数据就舍弃掉
SELECT * FROM test2 --这里面就该有3条记录
拨打流程 (--StartCall—— Attempt —— Connect —— CallEnd)
根据callend 来查找一共有多少个完整的拨打流程,
然后根据 callend 向上找最近的 Connect Attempt StartCall
他们才是一个完整的流程。最后test2 显示了 一共有多少完整流程的详情
展示如下:
ID StartCall Attempt Connect, Callend
1 2010-06-13 16:45:25 2010-06-13 16:45:28 2010-06-13 16:45:50 2010-06-13 16:46:05
2 2010-06-13 16:55:25 2010-06-13 16:55:28 2010-06-13 16:55:50 2010-06-13 16:56:05
3 2010-06-13 16:57:25 2010-06-13 16:57:28 2010-06-13 16:57:50 2010-06-13 16:57:05
对的,它的整个流程在数据库中的排列都是按顺序的,
不会是乱序的。(StartCall—— Attempt —— Connect —— CallEnd)
select
(select time from test b where b.id = a.id - 3) as StartCall,
(select time from test b where b.id = a.id - 2) as Attempt,
(select time from test b where b.id = a.id - 1) as Connect,
a.time as CallEnd
from test a
where a.NAME = 'CallEnd'select * from test2/*
ID StartCall Attempt Connect Callend
----------- ----------------------- ----------------------- ----------------------- -----------------------
1 2010-06-13 16:45:25.000 2010-06-13 16:45:28.000 2010-06-13 16:45:50.000 2010-06-13 16:46:05.000
2 2010-06-13 16:55:25.000 2010-06-13 16:55:28.000 2010-06-13 16:55:50.000 2010-06-13 16:56:05.000
3 2010-06-13 16:57:25.000 2010-06-13 16:57:28.000 2010-06-13 16:57:50.000 2010-06-13 16:57:05.000(3 行受影响)*/
哦,不好意思,忘记说一点了,他们 的先后顺序是
(StartCall—— Attempt —— Connect —— CallEnd)
但是他们相差不为1的。
可能是
(StartCall——org—— Attempt ——org—— Connect —— CallEnd)
select
(select top 1 time from test b where b.id < a.id and b.name = 'StartCall' order by id desc) as StartCall,
(select top 1 time from test b where b.id < a.id and b.name = 'Attempt' order by id desc) as Attempt,
(select top 1 time from test b where b.id < a.id and b.name = 'Connect' order by id desc) as Connect,
a.time as CallEnd
from test a
where a.NAME = 'CallEnd'select * from test2/*
ID StartCall Attempt Connect Callend
----------- ----------------------- ----------------------- ----------------------- -----------------------
1 2010-06-13 16:45:25.000 2010-06-13 16:45:28.000 2010-06-13 16:45:50.000 2010-06-13 16:46:05.000
2 2010-06-13 16:55:25.000 2010-06-13 16:55:28.000 2010-06-13 16:55:50.000 2010-06-13 16:56:05.000
3 2010-06-13 16:57:25.000 2010-06-13 16:57:28.000 2010-06-13 16:57:50.000 2010-06-13 16:57:05.000(3 行受影响)
if object_id('a') is not null drop table a
create table a
(
ID INT IDENTITY(1,1) PRIMARY KEY,
NAME VARCHAR(50),
TIME datetime
)
insert a
select 'StartCall', '2010-06-13 16:45:25' union all
select 'Attempt', '2010-06-13 16:45:28' UNION ALL
select 'Connect', '2010-06-13 16:45:50' UNION ALL
select 'CallEnd', '2010-06-13 16:46:05' UNION ALL
select 'StartCall', '2010-06-13 16:55:25' union all
select 'Attempt', '2010-06-13 16:55:28' UNION ALL
select 'Connect', '2010-06-13 16:55:50' UNION ALL
select 'CallEnd', '2010-06-13 16:56:05' UNION ALL
select 'StartCall', '2010-06-13 16:57:25' union all
select 'Attempt', '2010-06-13 16:57:28' UNION ALL
select 'Connect', '2010-06-13 16:57:50' UNION ALL
select 'CallEnd', '2010-06-13 16:57:05' UNION ALL
select 'StartCall', '2010-06-13 16:58:25' UNION ALL
select 'Attempt', '2010-06-13 16:58:25' UNION ALL
select 'Connect', '2010-06-13 16:58:25'
gocreate function f_a(@name varchar(50))
returns INT
as
begin
return (select ID from a where NAME=@name)
end
gocreate function f_b(@name varchar(50))
returns @table table (id int,name varchar(50))
as
begin
insert into @table
select ID,NAME from a where NAME=@name
return
end
goselect *
from a
where a.ID in (select (ID-3) ID from dbo.f_b('CallEnd'))
union
select *
from a
where a.ID in (select (ID-2) ID from dbo.f_b('CallEnd'))
union
select *
from a
where a.ID in (select (ID-1) ID from dbo.f_b('CallEnd'))
union
select *
from a
where a.ID in (select ID from dbo.f_b('CallEnd'))