select * from tab1 a
where ((select count(*) from tab1 where phone=a.phone and datediff(day,a.visittime,visittime)>0 and (result=2 or result=3))=1 and result=1) or
(exists(select * from tab1 where phone=a.phone and datediff(day,visittime,a.visittime)>0 and result=1) and (result=2 or result=3))
where ((select count(*) from tab1 where phone=a.phone and datediff(day,a.visittime,visittime)>0 and (result=2 or result=3))=1 and result=1) or
(exists(select * from tab1 where phone=a.phone and datediff(day,visittime,a.visittime)>0 and result=1) and (result=2 or result=3))
select * from tab1 where result = 1
union all
select * from tab1 a,(select * from tab1 where result = 1) b
where
a.result > 1
and
convert(varchar(8),a.visittime,112) > convert(varchar(8),a.visittime,112)
and
a.phone = b.phone) c
where c.phone in(select phone from tab1 a,(select * from tab1 where result = 1) b
where
a.result > 1
and
convert(varchar(8),a.visittime,112) > convert(varchar(8),a.visittime,112)
and
a.phone = b.phone)
from
(
select * from tablename a
where not exists (
select * from tablename
where phone=a.phone
and visittime>a.visittime
)
) as last,
(
select * from tablename a
where (
select count(*) from tablename
where phone=a.phone
and visittime>a.visittime
) =1
) as last2
where last.phone=last2.phone
and last.result in (2,3)
and last2.result =1
and datediff(day,last2.visittime,last.visittime)>0
union all
select last.*
from
(
select * from tablename a
where not exists (
select * from tablename
where phone=a.phone
and visittime>a.visittime
)
) as last,
(
select * from tablename a
where (
select count(*) from tablename
where phone=a.phone
and visittime>a.visittime
) =1
) as last2
where last.phone=last2.phone
and last.result in (2,3)
and last2.result =1
and datediff(day,last2.visittime,last.visittime)>0
长了点,没优化,应该还有其他方法,不时没答案。
phone visittime1 visittime2
--------------------------------------------------------
83121108 2004-08-12 20:47:00 2004-08-13 11:33:00
86329201 2004-08-14 11:12:00 2004-08-15 11:47:00那就没这么长了:select last.phone,last2.visittime as visittime1,last.visittime as visittime2
from
(
select * from tablename a
where not exists (
select * from tablename
where phone=a.phone
and visittime>a.visittime
)
) as last,
(
select * from tablename a
where (
select count(*) from tablename
where phone=a.phone
and visittime>a.visittime
) =1
) as last2
where last.phone=last2.phone
and last.result in (2,3)
and last2.result =1
and datediff(day,last2.visittime,last.visittime)>0
create table tab1(ID int,visittime datetime,phone varchar(10),result int)
insert tab1 select '337' ,'2004-08-10 13:07:00','83121108','2'
union all select '5403','2004-08-12 20:47:00','83121108','1'
union all select '6227','2004-08-13 11:33:00','83121108','2'
union all select '4614','2004-08-12 14:32:00','83515600','1'
union all select '4615','2004-08-12 14:32:00','83515600','2'
union all select '1416','2004-08-10 19:07:00','88522292','1'
union all select '1482','2004-08-10 19:30:00','88522292','2'
union all select '3019','2004-08-11 15:42:00','83235513','1'
union all select '3029','2004-08-11 15:45:00','83235513','3'
union all select '9064','2004-08-14 11:12:00','86329201','1'
union all select '9278','2004-08-15 11:47:00','86329201','3'
go--查询
select aa.*
from tab1 aa,tab1 a
where (aa.id=a.id
or aa.id=(
select min(id) from tab1
where phone=a.phone and id>a.id
))and(
a.result=1 and (
select min(id) from tab1
where phone=a.phone and id>a.id
)=(
select min(id) from tab1
where phone=a.phone and id>a.id
and a.visittime<convert(char(8),visittime,112)
and result in(2,3)))
go--删除测试
drop table tab1/*--测试结果ID visittime phone result
----------- ------------------------- ---------- --------
5403 2004-08-12 20:47:00.000 83121108 1
6227 2004-08-13 11:33:00.000 83121108 2
9064 2004-08-14 11:12:00.000 86329201 1
9278 2004-08-15 11:47:00.000 86329201 3(所影响的行数为 4 行)
--*/
from tab1 aa,tab1 a
where (aa.id=a.id
or aa.id=(
select min(id) from tab1
where phone=a.phone and id>a.id
))and(
a.result=1 and (
select min(id) from tab1
where phone=a.phone and id>a.id
)=(
select min(id) from tab1
where phone=a.phone and id>a.id
and result between 2 and 3
and a.visittime<convert(char(8),visittime,112)))
你的结果是正确的.谢谢
现在这个表大概5万条数据
的一张方法大概8秒,第二种方法10秒左右.pbsql(风云)
这种方法提取的数据是result前后不同的数据,但不是前一条数据result=1,
还有些出现三条phone相同的数据...我之前取的也是这样子:(
你的语句不能运行,,,,谢谢! CSDMN(冒牌经理 V0.2)
看的有点头昏,呵呵,谢谢你还记得我的上一贴:)