表A
F_code F_phone F_state F_dates
1 123456 1 2008-05-13 12:20:20
1 123456 2 2008-05-13 12:20:25
1 123456 3 2008-05-13 12:20:30
2 222222 1 2008-05-13 12:25:40
2 222222 3 2008-05-13 12:25:45要求查询出F_State没有2的记录数,并且算出时间差
结果如下:
F_code F_phone 起始时间(F_state=1的时间) 终止时间(F_State=3的时间) 时间差
2 2222222 2008-05-13 12:25:40 2008-05-13 12:25:45 00:00:05F_code=1的记录由于有F_state=2这条记录所以不要统计进来
F_code F_phone F_state F_dates
1 123456 1 2008-05-13 12:20:20
1 123456 2 2008-05-13 12:20:25
1 123456 3 2008-05-13 12:20:30
2 222222 1 2008-05-13 12:25:40
2 222222 3 2008-05-13 12:25:45要求查询出F_State没有2的记录数,并且算出时间差
结果如下:
F_code F_phone 起始时间(F_state=1的时间) 终止时间(F_State=3的时间) 时间差
2 2222222 2008-05-13 12:25:40 2008-05-13 12:25:45 00:00:05F_code=1的记录由于有F_state=2这条记录所以不要统计进来
(
select f_code , min(F_dates) min_F_dates , max(F_dates) max_F_dates from
(
select * from A where f_code not in (select f_code from A where F_state = 2)
) t
group by f_code
) m
insert into @tb select 1,'123456',1,'2008-05-13 12:20:20'
insert into @tb select 1,'123456',2,'2008-05-13 12:20:25'
insert into @tb select 1,'123456',3,'2008-05-13 12:20:30'
insert into @tb select 2,'222222',1,'2008-05-13 12:25:40'
insert into @tb select 2,'222222',3,'2008-05-13 12:25:45'select f_code,F_phone,
min(F_dates) as '最小',
max(F_dates) as '最大'
from @tb t where not exists(
select * from @tb where f_code=t.f_code and F_state=2
)
group by f_code,F_phone
from 表A a1 inner join 表A a3 on a1.F_code = a3.F_Code AND a1.F_phone = a3.F_phone
and a1.F_state = 1 and a3.F_state = 3
where not exists( select 1 from 表A where F_Code = a1.F_Code AND F_phone = a1.F_phone
and F_state = 2 )
insert into tb select 1 , 123456 , 1, '2008-05-13 12:20:20'
insert into tb select 1 , 123456 , 2 , '2008-05-13 12:20:25'
insert into tb select 1 , 123456 , 3 , '2008-05-13 12:20:30'
insert into tb select 2 , 222222 , 1 , '2008-05-13 12:25:40'
insert into tb select 2 , 222222 , 3 , '2008-05-13 12:25:45' select f_code,f_phone,min(f_dates) f_mindates,
max(f_dates) f_maxdates,
datediff(second,min(f_dates),max(f_dates)) diff
from tb a
where not exists(select 1 from tb where f_code=a.f_code and f_state=2)
group by f_code,f_phonedrop table tb/*
f_code f_phone f_mindates f_maxdates diff
----------- ----------- ------------------------------------------------------ ------------------------------------------------------ -----------
2 222222 2008-05-13 12:25:40.000 2008-05-13 12:25:45.000 5(所影响的行数为 1 行)*/
insert into @tb select 1,'123456',1,'2008-05-13 12:20:20'
insert into @tb select 1,'123456',2,'2008-05-13 12:20:25'
insert into @tb select 1,'123456',3,'2008-05-13 12:20:30'
insert into @tb select 2,'222222',1,'2008-05-13 12:25:40'
insert into @tb select 2,'222222',3,'2008-05-13 12:25:45'select f_code,F_phone,
min(F_dates) as '最小',
max(F_dates) as '最大',
datediff(ss,min(F_dates),max(F_dates)) as '时间差'
from @tb t where not exists(
select * from @tb where f_code=t.f_code and F_state=2
)
group by f_code,F_phonef_code F_phone 最小 最大 时间差
2 222222 2008-05-13 12:25:40.000 2008-05-13 12:25:45.000 5
where f_code not in(select f_code from 表A where f_state=2)
group by f_code,f_phone