SQL表create table myOrder_DE
(
deid_int int identity(1,1) not null,
orderid_int int null,
deType_nvarchar nvarchar(5) null,
deCreateDatetime_datetime datetime null
)
go
insert into myOrder_DE values(1001,'AD','2011-02-03 16:30:00')
insert into myOrder_DE values(1001,'PD','2011-02-03 16:33:10')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:37:05')
insert into myOrder_DE values(1001,'AD','2011-02-03 16:42:00')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:50:10')
insert into myOrder_DE values(1001,'AD','2011-02-03 16:55:00')
insert into myOrder_DE values(1001,'CD','2011-02-03 16:57:00')insert into myOrder_DE values(1003,'AD','2011-02-03 16:20:00')
insert into myOrder_DE values(1003,'PD','2011-02-03 16:23:10')
insert into myOrder_DE values(1003,'IR','2011-02-03 16:27:05')insert into myOrder_DE values(1008,'AD','2011-02-03 16:12:00')
insert into myOrder_DE values(1008,'IR','2011-02-03 16:20:10')
insert into myOrder_DE values(1008,'IR','2011-02-03 16:35:00')
insert into myOrder_DE values(1008,'CD','2011-02-03 16:47:00')问题:
我如何求状态为IR到下一个IR之前时,订单的平均值
如订单1001:
insert into myOrder_DE values(1001,'IR','2011-02-03 16:37:05')
insert into myOrder_DE values(1001,'AD','2011-02-03 16:42:00')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:50:10')我求的是第一个IR到AD之间的时间平均值
这只是举个例子。
里面表里面有N个IR。那么我要怎么写这个SQL呢?
(
deid_int int identity(1,1) not null,
orderid_int int null,
deType_nvarchar nvarchar(5) null,
deCreateDatetime_datetime datetime null
)
go
insert into myOrder_DE values(1001,'AD','2011-02-03 16:30:00')
insert into myOrder_DE values(1001,'PD','2011-02-03 16:33:10')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:37:05')
insert into myOrder_DE values(1001,'AD','2011-02-03 16:42:00')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:50:10')
insert into myOrder_DE values(1001,'AD','2011-02-03 16:55:00')
insert into myOrder_DE values(1001,'CD','2011-02-03 16:57:00')insert into myOrder_DE values(1003,'AD','2011-02-03 16:20:00')
insert into myOrder_DE values(1003,'PD','2011-02-03 16:23:10')
insert into myOrder_DE values(1003,'IR','2011-02-03 16:27:05')insert into myOrder_DE values(1008,'AD','2011-02-03 16:12:00')
insert into myOrder_DE values(1008,'IR','2011-02-03 16:20:10')
insert into myOrder_DE values(1008,'IR','2011-02-03 16:35:00')
insert into myOrder_DE values(1008,'CD','2011-02-03 16:47:00')问题:
我如何求状态为IR到下一个IR之前时,订单的平均值
如订单1001:
insert into myOrder_DE values(1001,'IR','2011-02-03 16:37:05')
insert into myOrder_DE values(1001,'AD','2011-02-03 16:42:00')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:50:10')我求的是第一个IR到AD之间的时间平均值
这只是举个例子。
里面表里面有N个IR。那么我要怎么写这个SQL呢?
(
deid_int int identity(1,1) not null,
orderid_int int null,
deType_nvarchar nvarchar(5) null,
deCreateDatetime_datetime datetime null
)
insert into myOrder_DE values(1001,'AD','2011-02-03 16:30:00')
insert into myOrder_DE values(1001,'PD','2011-02-03 16:33:10')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:37:05')
insert into myOrder_DE values(1001,'AD','2011-02-03 16:42:00')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:50:10')
insert into myOrder_DE values(1001,'AD','2011-02-03 16:55:00')
insert into myOrder_DE values(1001,'CD','2011-02-03 16:57:00')
insert into myOrder_DE values(1003,'AD','2011-02-03 16:20:00')
insert into myOrder_DE values(1003,'PD','2011-02-03 16:23:10')
insert into myOrder_DE values(1003,'IR','2011-02-03 16:27:05')
insert into myOrder_DE values(1008,'AD','2011-02-03 16:12:00')
insert into myOrder_DE values(1008,'IR','2011-02-03 16:20:10')
insert into myOrder_DE values(1008,'IR','2011-02-03 16:35:00')
insert into myOrder_DE values(1008,'CD','2011-02-03 16:47:00')
go
--第一个IR:
select * from myOrder_DE a where not exists(
select 1 from myOrder_DE where orderid_int=a.orderid_int and deCreateDatetime_datetime>a.deCreateDatetime_datetime
)
/*
deid_int orderid_int deType_nvarchar deCreateDatetime_datetime
----------- ----------- --------------- -------------------------
7 1001 CD 2011-02-03 16:57:00.000
10 1003 IR 2011-02-03 16:27:05.000
14 1008 CD 2011-02-03 16:47:00.000(3 行受影响)
*/
--但不知道所谓的时间平均值是什么意思.
go
drop table myOrder_DE
你这个不是我想要的啊;不过还是谢谢了我要表达的意思就是:
我求IR这一个状态类型的时间到下一个IR状态之前的那个非IR状态的时间差然后根据这个时间差除以有几个IR状态的和;
这其中有可能N个IR状态或者只有一个IR状态麻烦了
不好意思。。太忙了。。可能没表达清楚意思
IR状态到下一个IR状态之前的那个F非IR状态的时间平均值
insert into myOrder_DE values(1001,'PD','2011-02-03 16:33:10')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:37:05')
insert into myOrder_DE values(1001,'AD','2011-02-03 16:42:00')
insert into myOrder_DE values(1001,'CC','2011-02-03 16:43:00')
insert into myOrder_DE values(1001,'DD','2011-02-03 16:44:00')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:50:10')
insert into myOrder_DE values(1001,'AD','2011-02-03 16:55:00')
insert into myOrder_DE values(1001,'CD','2011-02-03 16:57:00')如:第一个IR是时间是:2011-02-03 16:37:05
下一个IR时间是:2011-02-03 16:50:10
那么下一个IR之前的那个时间是:2011-02-03 16:44:00
我求的就是:2011-02-03 16:37:05到这个2011-02-03 16:44:00时间差的平均值==365秒除以2(因为有2个IR)
2. 要求的是第一个IR到第二个IR之间时间差的平均值,再求第一个IR到第三个IR之间时间差的平均值,
再求第一个IR到第四个IR之间时间差的平均值.....?
还是求第一个IR到第二个IR之间时间差的平均值,再求第二个IR到第三个IR之间时间差的平均值,
再求第三个IR到第四个IR之间时间差的平均值.....?
再求第三个IR到第四个IR之间时间差的平均值.....
(
deid_int int identity(1,1) not null,
orderid_int int null,
deType_nvarchar nvarchar(5) null,
deCreateDatetime_datetime datetime null
)
goinsert into myOrder_DE values(1001,'AD','2011-02-03 16:30:00')
insert into myOrder_DE values(1001,'PD','2011-02-03 16:33:10')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:37:05')
insert into myOrder_DE values(1001,'AD','2011-02-03 16:42:00')
insert into myOrder_DE values(1001,'CC','2011-02-03 16:43:00')
insert into myOrder_DE values(1001,'DD','2011-02-03 16:44:00')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:50:10')
insert into myOrder_DE values(1001,'AD','2011-02-03 16:55:00')
insert into myOrder_DE values(1001,'CD','2011-02-03 16:57:00')
insert into myOrder_DE values(1001,'IR','2011-02-03 16:58:10')select a.*,datediff(ss,a.deCreateDatetime_datetime,b.deCreateDatetime_datetime)/2 as 时间差的平均值
from myOrder_DE a inner join myOrder_DE b
on a.deType_nvarchar=N'IR'
and a.deid_int<>(select max(deid_int) from myOrder_DE where deType_nvarchar=N'IR')
and b.deid_int=(select top 1 deid_int from myOrder_DE
where deid_int<(select top 1 deid_int from myOrder_DE c
where c.deType_nvarchar=N'IR' and c.deid_int>a.deid_int order by c.deid_int) order by deid_int desc)
order by b.deCreateDatetime_datetime
用了楼上的报错。。我晕
deid_int orderid_int deType_nvarchar deCreateDatetime_datetime 时间差的平均值
----------- ----------- --------------- ----------------------------- -----------
3 1001 IR 2011-02-03 16:37:05.000 207
7 1001 IR 2011-02-03 16:50:10.000 205(所影响的行数为 2 行)
我如何取得IR下一个状态的时间(下一个状态要是非IR的)
(select top 1 deCreateDatetime_datetime from myOrder_DE d where deType_nvarchar<>N'IR'
and d.deCreateDatetime_datetime>a.deCreateDatetime_datetime order by d.deCreateDatetime_datetime)
as IR下一个状态的时间from myOrder_DE a inner join myOrder_DE b
on a.deType_nvarchar=N'IR'
and a.deid_int<>(select max(deid_int) from myOrder_DE where deType_nvarchar=N'IR')
and b.deid_int=(select top 1 deid_int from myOrder_DE
where deid_int<(select top 1 deid_int from myOrder_DE c
where c.deType_nvarchar=N'IR' and c.deid_int>a.deid_int order by c.deid_int) order by deid_int desc)
order by b.deCreateDatetime_datetime