create table t1 (id number(5),ph number(11),my number(5));insert into t1 values (1,139139114,60); insert into t1 values (4,139139139,50); insert into t1 values (5,139139134,40); insert into t1 values (7,139139134,50); insert into t1 values (8,139139131,70); insert into t1 values (9,139139133,80); insert into t1 values (11,139139133,30); insert into t1 values (12,139139133,20); insert into t1 values (13,139139144,80); insert into t1 values (14,139139131,30); insert into t1 values (16,139139134,33); commit;
select distinct a.id,a.ph,a.my from (select t1.*,rownum r1 from t1) a,(select t1.*,rownum r2 from t1) b where a.ph=b.ph and (a.r1=b.r2-1 or a.r1-1=b.r2) order by id id ph my --------------------------------------- 1 5 139139134 40 2 7 139139134 50 3 9 139139133 80 4 11 139139133 30 5 12 139139133 20
连续记录 单用group肯定行不通吧?
select 号码,count(riqi) from (select 号码,trunc(时间) riqi from 表名) group by 号码 having count(riqi)>1
select 号码,count(riqi) from (select 号码,trunc(时间) riqi from 表名) group by 号码 having count(riqi)>1
不还是那个老问题吗。你们没看到他的要求聊连续。就是说接连两笔都是同一个人才行。中间隔个人就不算。不用lead lag 就要用嵌套子查询。其实就是自己写子查询把lead lag的功能实现了。
t (date no),date是主键with t1 as ( select rownum id,t.no,to_char(t.date,'YYYYMMDD') date1,t.* from t order by date ) select date,no from t1 a where exists( select 1 from t1 b where (a.id=b.id+1 or a.id=b.id-1) and a.id=b.id and a.date1=b.date1 )ok逻辑上应该没问题了。不过效率就有待商榷。哪位达人优化下吧。
第三行t.no,去掉 本来只想留no的后来想想还是把主键带上吧。忘了删t.no了
8楼的倒数第三行改成a.no=b.no老是笔误
连续缴费 并且金额不同吗? with t1 as ( select rownum id,to_char(t.date,'YYYYMMDD') date1,t.* from t order by date ) select date,no from t1 a where exists( select 1 from t1 b where (a.id=b.id+1 or a.id=b.id-1) and a.no=b.no and a.fee!=b.fee and a.date1=b.date1 )
这样不可以吗?我没有测试,个人觉得这样就可以出来了。 错了不要喷我。table A: paydate 插入时间,money 充值金额, uid 用户IDselect a.uid, to_char(a.paydate,'yyyy-mm-dd') tmpdate, count(a.money) from A a group by a.uid, a.tmpdate having(count(a.money) > 1);
GROUP BY 号码
create table t1 (id number(5),ph number(11),my number(5));insert into t1 values (1,139139114,60);
insert into t1 values (4,139139139,50);
insert into t1 values (5,139139134,40);
insert into t1 values (7,139139134,50);
insert into t1 values (8,139139131,70);
insert into t1 values (9,139139133,80);
insert into t1 values (11,139139133,30);
insert into t1 values (12,139139133,20);
insert into t1 values (13,139139144,80);
insert into t1 values (14,139139131,30);
insert into t1 values (16,139139134,33);
commit;
select distinct a.id,a.ph,a.my
from (select t1.*,rownum r1 from t1) a,(select t1.*,rownum r2 from t1) b
where a.ph=b.ph and (a.r1=b.r2-1 or a.r1-1=b.r2)
order by id
id ph my
---------------------------------------
1 5 139139134 40
2 7 139139134 50
3 9 139139133 80
4 11 139139133 30
5 12 139139133 20
group by 号码
having count(riqi)>1
group by 号码
having count(riqi)>1
(
select rownum id,t.no,to_char(t.date,'YYYYMMDD') date1,t.*
from t
order by date
)
select date,no
from t1 a
where exists(
select 1 from t1 b
where (a.id=b.id+1 or a.id=b.id-1)
and a.id=b.id
and a.date1=b.date1
)ok逻辑上应该没问题了。不过效率就有待商榷。哪位达人优化下吧。
with t1 as
(
select rownum id,to_char(t.date,'YYYYMMDD') date1,t.*
from t
order by date
)
select date,no
from t1 a
where exists(
select 1 from t1 b
where (a.id=b.id+1 or a.id=b.id-1)
and a.no=b.no
and a.fee!=b.fee
and a.date1=b.date1
)
错了不要喷我。table A: paydate 插入时间,money 充值金额, uid 用户IDselect a.uid, to_char(a.paydate,'yyyy-mm-dd') tmpdate, count(a.money) from A a
group by a.uid, a.tmpdate having(count(a.money) > 1);