Question

sql查询同一个表中id相同的两条数据的时间差

只有一个表，phone statedate salenum
15125462562 2011-07-06 12:22:22 A001
15465236150 2011-07-06 12:23:55 A001
15125402154 2011-07-06 12:24:22 A002
15465032510 2011-07-06 12:50:55 A002
salenum相同的两条算一笔业务，我想要一笔业务中的两条数据的statedate小于两分钟的条数！
自己解决了，谢谢哥几个了！~

Answer 1

SELECT COUNT(*)
FROM
(SELECT SALENUM,
MAX(DECODE(RN,1,STATEDATE)) END_TIME,
MAX(DECODE(RN,2,STATEDATE)) STAT_TIME
FROM
(SELECT PHONE,
STATEDATE,
SALENUM,
RANK()OVER(PARTITION BY SALENUM ORDER BY STATEDATE DESC) RN
FROM TABLE_NAME)
GROUP BY SALENUM )
HAVING DATEDIFF(MINUTE,END_TIME,STAT_TIME)< 2
这个应该能够满足，不知道是否有语法错误！好久没用SQL 如果有麻烦你调试下！

Answer 2

select count(*)
from (
select salenum,min(a.statedate) as minstatedate,max(a.statedate) as maxstatedate
from A
) as t1
where datediff(minute,t1.minstatedate,t1.maxstatedate) > 2

Answer 3

请问salenum都是两条两条的吗？
如果是的话。这样试试
SELECT COUNT(*)
FROM TALBE
GROUP BY salenum
HAVING DATEDIFF (minute, MIN( statedate),MAX(statedate)) < 2

Answer 4

SELECT a.salenum,MAX(a.statedate),MIN(a.statedate) FROM testa a
GROUP BY a.salenum
HAVING (MAX(statedate)-MIN( statedate))*60*24<2;

Answer 5

假设表1，表2的字段1是相同的，则： select a.字段1a.字段n,b.字段1b.字段n from 表1 a，表2 b where a.字段1=b.字段1 不