select distinct a.ID from tabname a where exists(select 1 from tabname where id=a.id and month=a.month-1 and nvl(值,0)<>0) --上月有数据 and exists(select 1 from tabname where id=a.id and month=a.month and nvl(值,0)<>0) --本月又数据 and exists(select 1 from tabname where id=a.id and month=a.month+1 and nvl(值,0)<>0) --下月有数据
嘿嘿,很烂的一条语句,你试试看 select * from (select t.*,(month - rownum) zsf from table) tt where tt.id in ( select t.id from (select id,month,(month - rownum) zz from table) t group by t.id having count(t.zz) = 3 ) and tt.zsf in (select avg(t.zz) from (select id,month,(month - rownum) zz from table) t group by t.id having count(t.zz) = 3 )
to: zsfww1205(在帮助别人中学习,在学习中帮助别人) 你的语句都看不懂啊
to:libin_ftsafe(子陌红尘) 你的语句我试了不行
select * from (select count(*) over (PARTITION by DIFFDATE) diffcount,id,month from ( SELECT id,month ,add_months(month ,-ROW_NUMBER() OVER(ORDER BY id,month) ) DIFFDATE FROM t ) )where diffcount = 3
sorry ,group by t.id 应该是group by (t.id,t.zz),嘿嘿,有点长
select * from (select t.*,(month - rownum) zsf from atest1 t) tt where tt.id in ( select t.id from (select id,(month - rownum) zz from atest1 where id ='100') t group by (t.id,t.zz) having count(t.zz) = 3 ) and tt.zsf in (select avg(t.zz) from (select id,(month - rownum) zz from atest1) t group by (t.id,t.zz) having count(t.zz) = 3 ) 输出:ID MONTH ZSF ---------- ---------- ---------- 100 200205 200203 100 200206 200203 100 200207 200203
思路是这样的,如果月份连续的话,减去一个rownum(系统自带的排序函数,结果是一组连续的值)以后的值(令为zsf,嘿嘿,习惯动作) 会相等,这样的话,按id,zsf分组(group by (t.id,t.zz)),如果分组以后的纪录数量为3(having count(t.zz) = 3),就是满足你条件的纪录但是找出来以后还要输出,所以找出id和zsf都在纪录里的表中的纪录。至于那个avg(t.zz),完全可以改为t.zz.明白了吗??
上面我写的是month日期型的场合,如果不是日期型可以这样 select * from (select count(*) over (PARTITION by DIFFDATE) diffcount,id,month from ( SELECT id,month ,month -ROW_NUMBER() OVER(ORDER BY id,month) DIFFDATE FROM t ) )where diffcount = 3
to:zsfww1205(在帮助别人中学习,在学习中帮助别人) 问题是我的id有上万个啊 我这样写的 select a.id,a.month from table a and exists(select 1 from table where id=a.id and month=a.month-1 ) --上月有数据 and exists(select 1 from table where id=a.id and month=a.month) --本月有数据 and exists(select 1 from table where id=a.id and month=a.month+1 ) --下月有数据
又想到有点问题了,这样应该没错了 select * from (select count(*) over (PARTITION by id,DIFFDATE) diffcount,id,month from ( SELECT id,month ,month-ROW_NUMBER() OVER(ORDER BY id,month) DIFFDATE FROM t ) )where diffcount = 3
to:duanzilin(寻) 你能对你的语句说明一下么,我看不懂
mayongzhi(学习奋斗) duanzilin(寻) 你能对你的语句说明一下么,我看不懂他的思路和我差不多的,不过他比我厉害,嘿嘿,写得很简单 SELECT id,month ,month-ROW_NUMBER() OVER(ORDER BY id,month) DIFFDATE FROM t 也是找出连续月份(减去一个rownum会相等), count(*) over (PARTITION by id,DIFFDATE) diffcount 按id ,DIFFDATE 分区,找出相同id,连续月份的记录数,然后找出diffcount = 3得纪录,嘿嘿,高手!!
distinct a.ID
from
tabname a
where
exists(select 1 from tabname where id=a.id and month=a.month-1 and nvl(值,0)<>0) --上月有数据
and
exists(select 1 from tabname where id=a.id and month=a.month and nvl(值,0)<>0) --本月又数据
and
exists(select 1 from tabname where id=a.id and month=a.month+1 and nvl(值,0)<>0) --下月有数据
id month
...
100 200201
100 200205
100 200206
100 200207
100 200210
...
101 200201
101 200202
101 200205
101 200206
....
102 200201
...
如上表中,
100 200205
100 200206
100 200207
就是我要的数据
因为有连续三个月的记录
怎样写呢?
select * from (select t.*,(month - rownum) zsf from table) tt where tt.id in
(
select t.id from
(select id,month,(month - rownum) zz from table) t
group by t.id having count(t.zz) = 3
)
and tt.zsf in
(select avg(t.zz) from
(select id,month,(month - rownum) zz from table) t
group by t.id having count(t.zz) = 3
)
你的语句都看不懂啊
你的语句我试了不行
(select count(*) over (PARTITION by DIFFDATE) diffcount,id,month from
(
SELECT id,month ,add_months(month ,-ROW_NUMBER() OVER(ORDER BY id,month) ) DIFFDATE
FROM t
) )where diffcount = 3
where tt.id in
(
select t.id from
(select id,(month - rownum) zz from atest1 where id ='100') t
group by (t.id,t.zz) having count(t.zz) = 3
)
and tt.zsf in
(select avg(t.zz) from
(select id,(month - rownum) zz from atest1) t
group by (t.id,t.zz) having count(t.zz) = 3
)
输出:ID MONTH ZSF
---------- ---------- ----------
100 200205 200203
100 200206 200203
100 200207 200203
select * from
(select count(*) over (PARTITION by DIFFDATE) diffcount,id,month from
(
SELECT id,month ,month -ROW_NUMBER() OVER(ORDER BY id,month) DIFFDATE
FROM t
) )where diffcount = 3
问题是我的id有上万个啊
我这样写的
select
a.id,a.month
from
table a
and
exists(select 1 from table where id=a.id and month=a.month-1 ) --上月有数据
and
exists(select 1 from table where id=a.id and month=a.month) --本月有数据
and
exists(select 1 from table where id=a.id and month=a.month+1 ) --下月有数据
select * from
(select count(*) over (PARTITION by id,DIFFDATE) diffcount,id,month from
(
SELECT id,month ,month-ROW_NUMBER() OVER(ORDER BY id,month) DIFFDATE
FROM t
) )where diffcount = 3
你能对你的语句说明一下么,我看不懂
duanzilin(寻)
你能对你的语句说明一下么,我看不懂他的思路和我差不多的,不过他比我厉害,嘿嘿,写得很简单
SELECT id,month ,month-ROW_NUMBER() OVER(ORDER BY id,month) DIFFDATE FROM t
也是找出连续月份(减去一个rownum会相等),
count(*) over (PARTITION by id,DIFFDATE) diffcount 按id ,DIFFDATE 分区,找出相同id,连续月份的记录数,然后找出diffcount = 3得纪录,嘿嘿,高手!!
水平差太远了
学习ing