表A用户 状态 时间aa 1 2010-1-1
aa 2 2010-1-9
bb 1 2010-1-1
bb 2 2010-1-2状态1表示订购 2表去退订
我要查找在订购后72小时内退订的用户 谢谢 在线等。
aa 2 2010-1-9
bb 1 2010-1-1
bb 2 2010-1-2状态1表示订购 2表去退订
我要查找在订购后72小时内退订的用户 谢谢 在线等。
INSERT INTO @tb
SELECT 'aa', 1,'2010-1-1' UNION ALL
SELECT 'aa', 2,'2010-1-9' UNION ALL
SELECT 'bb', 1,'2010-1-1' UNION ALL
SELECT 'bb', 2,'2010-1-2'SELECT * FROM
@tb a JOIN @tb b ON a.yh = b.yh
WHERE a.zt = 1 AND b.zt = 2
AND DATEDIFF(hour,a.sj,b.sj) < 72
from tb t1,tb t2
where t1.user=t2.user and t1.status=1 and t2.status=2
and datediff(day,t1.time,t2.time)<=3
--随手写的,错了莫怪
select distinct 用户
from T as A
where 状态 =1
and datediff(day,时间 ,isnull((select min(时间) from T where 用户=A.用户 and 状态 =2 and 时间>A.时间),时间) )>=3
aa 2 2010-1-2
aa 1 2010-1-2
aa 2 2010-1-3这种情况的话是不是有些不准确了呢 ??????
where zt=2
and datediff(hh,(select top 1 sj from #tb where yh=t.yh and zt=1),t.sj)>72yh zt sj
---- ----------- -----------------------
aa 2 2010-01-09 00:00:00.000(1 row(s) affected)
在我刚才发的修改一下SELECT * FROM
@tb a JOIN @tb b ON a.yh = b.yh
WHERE a.zt = 1 AND b.zt = 2 AND a.sj <= b.sj
AND DATEDIFF(hour,a.sj,b.sj) < 72试试?
create TABLE #tb(yh CHAR(2),zt INT,sj DATETIME)
INSERT INTO #tb
SELECT 'aa', 1,'2010-1-1' UNION ALL
SELECT 'aa', 2,'2010-1-9' UNION ALL
SELECT 'aa', 1,'2010-3-1' UNION ALL
SELECT 'aa', 2,'2010-3-3' UNION ALL
SELECT 'aa', 1,'2010-4-1' UNION ALL
SELECT 'aa', 2,'2010-4-9' UNION ALL
SELECT 'bb', 1,'2010-1-1' UNION ALL
SELECT 'bb', 2,'2010-1-2'select * from #tbselect * from #tb t
where zt=2
and datediff(hh,(select max(sj) from #tb where yh=t.yh and zt=1 and sj<t.sj),t.sj)>72yh zt sj
---- ----------- -----------------------
aa 2 2010-01-09 00:00:00.000
aa 2 2010-04-09 00:00:00.000(2 row(s) affected)
@tb a JOIN @tb b ON a.yh = b.yh
WHERE a.zt = 1 AND b.zt = 2 AND a.sj <= b.sj
AND DATEDIFF(hour,a.sj,b.sj) < 72
和select * from #tb t
where zt=2
and datediff(hh,(select max(sj) from #tb where yh=t.yh and zt=1 and sj<t.sj),t.sj)>72
结果不一样 不知道谁是对的
我的有问题,改为:create TABLE #tb(yh CHAR(2),zt INT,sj DATETIME)
INSERT INTO #tb
SELECT 'aa', 1,'2010-1-1' UNION ALL
SELECT 'aa', 2,'2010-1-9' UNION ALL
SELECT 'aa', 1,'2010-3-1' UNION ALL
SELECT 'aa', 2,'2010-3-3' UNION ALL
SELECT 'aa', 1,'2010-4-1' UNION ALL
SELECT 'aa', 2,'2010-4-9' UNION ALL
SELECT 'bb', 1,'2010-1-1' UNION ALL
SELECT 'bb', 2,'2010-1-2'select * from #tbselect * from #tb t
where zt=2
and datediff(hh,(select max(sj) from #tb where yh=t.yh and zt=1 and sj<t.sj),t.sj)<=72yh zt sj
---- ----------- -----------------------
aa 2 2010-03-03 00:00:00.000
bb 2 2010-01-02 00:00:00.000(2 row(s) affected)
where zt=2
and datediff(hh,(select max(sj) from #tb where yh=t.yh and zt=1 and sj<t.sj),t.sj)<=72
SELECT * FROM
@tb a JOIN @tb b ON a.yh = b.yh
WHERE a.zt = 1 AND b.zt = 2 AND a.sj <= b.sj
AND DATEDIFF(hour,a.sj,b.sj) <= 72正解