帮小弟我看一上这几个sql 如何写

帮我看一下这几个sql 怎么写？
如下A表记录用户每次动作

mobile time action re_value（返回值）
13631521739 20120915 1（开通） 0(成功）
15143728915 20120915 1（开通） 0(成功）
15143728915 20120915 1（开通） 2(重复开通）
15143728915 20120918 2（注销） 0(成功）
13631521739 20120925 2（注销） 0(成功）

1. 查询在开通四天之内又注销的用户

2.查询在开通四天后又注销的用户

[解决办法]
select a.mobile, a.time as timea, nvl(b.time,20990101) as timeb
from (select mobile, time
from ttt
where action = 1
and re_value = 0) a
left join (select mobile, time
from ttt
where action = 2
and re_value = 0) b on a.mobile = b.mobile；
至于4天内外自己外面包
[解决办法]


select ta.mobile from, ta.maxt, tb.maxt from 
(select mobile, max(time) maxt, action from a group by mobile where action=1) ta,
(select mobile, max(time) maxt, action from a group by mobile where action=2) tb
where ta.mobile=tb.mobile and round(to_date(tb.maxt,'yyyymmdd')-to_date(ta.maxt,'yyyymmdd')) = 4;
不知道行不行，反正我没试。

[解决办法]


 with t as(
    select '13631521739' as moble,TO_DATE('20120915','YYYYMMDD') as time,'1' as action,'0' as re_value from dual 
    union
    select '15143728915' as moble,TO_DATE('20120915','YYYYMMDD') as time,'1' as action,'0' as re_value from dual 
    union
    select '15143728915' as moble,TO_DATE('20120915','YYYYMMDD') as time,'1' as action,'2' as re_value from dual
    union
    select '15143728915' as moble,TO_DATE('20120918','YYYYMMDD') as time,'2' as action,'0' as re_value from dual
    union
    select '13631521739' as moble,TO_DATE('20120925','YYYYMMDD') as time,'2' as action,'0' as re_value from dual
  )
--四天以及之内的
SELECT A.MOBLE,A.TIME,B.TIME,TRUNC(B.TIME-A.TIME) AS DAY
  FROM T A, T B
 WHERE A.MOBLE = B.MOBLE
   AND A.RE_VALUE != 2 AND B.RE_VALUE != 2
   AND A.ACTION = 1  AND B.ACTION= 2
   AND TRUNC(B.TIME-A.TIME)&lt;=4
--四天之后的 
 
SELECT A.MOBLE,A.TIME,B.TIME,TRUNC(B.TIME-A.TIME) AS DAY
  FROM T A, T B
 WHERE A.MOBLE = B.MOBLE
   AND A.RE_VALUE != 2 AND B.RE_VALUE != 2
   AND A.ACTION = 1  AND B.ACTION= 2
   AND TRUNC(B.TIME-A.TIME)&gt;4

[解决办法]
结果,四天之内的是:
MOBLETIMETIMEDAY
1514372891515-九月-1218-九月-123

四天之后的是:
MOBLETIMETIMEDAY
11363152173915-九月-1225-九月-1210
[解决办法]


select X.mobile as "四天之内注销的用户",X.time as "注销时间" from 
(
select aaa.time,aaa.mobile from 
(select * from A t1 where t1.mobile in( select mobile from A where re_value=0 group by mobile having count(1)&gt;1)) aaa 
where action=2
) X ,
(
select bbb.time,bbb.mobile from 
(select * from A t1 where t1.mobile in( select mobile from A where re_value=0 group by mobile having count(1)&gt;1)) bbb 
where action=1 
) Y where X.mobile=Y.mobile and date_format(X.time,'%Y%m%d')-date_format(Y.time,'%Y%m%d')&gt;4 group by X.mobile,Y.time

[解决办法]
楼上是mysql，以下是Oracle


with q1 as (select * from A t1 where t1.mobile in( select mobile from A where re_value=0 group by mobile having count(1)&gt;1))
select distinct(q3.mobile) "四天之内注销的用户",q3.time "注销时间"
 from 
 (select time,mobile from q1 where action=1) q2,
 (select time,mobile from q1 where action=2) q3 
where q3.mobile=q2.mobile and to_date(q3.time,'yyyyMMdd')-to_date(q2.time,'yyyyMMdd')&gt;4

[解决办法]
select X.mobile as "四天之内注销的用户",X.time as "注销时间" from
(
select aaa.time,aaa.mobile from
(select * from A t1 where t1.mobile in( select mobile from A where re_value=0 group by mobile having count(1)>1)) aaa
where action=2
) X ,
(
select bbb.time,bbb.mobile from
(select * from A t1 where t1.mobile in( select mobile from A where re_value=0 group by mobile having count(1)>1)) bbb
where action=1
) Y where X.mobile=Y.mobile and date_format(X.time,'%Y%m%d')-date_format(Y.time,'%Y%m%d')>4

帮小弟我看一上这几个sql 如何写

热点推荐