select t1.cardno1,t1.cardno2,count(t1.*) from trans t1,trans t2 where t1.cardno1=t2.cardno2 and t1.cardno2=t2.cardno1
group by t1.cardno1,t1.cardno2
having count(t1.*) >= 10
select flag, count(*)
from (select c1,
c2,
(case
when c1 < c2 then
c1 || ',' || c2
else
c2 || ',' || c1
end) flag
from t1 a) v1
group by flag
having count(*) >= 10
select flag, count(*)
from (select c1,
c2,
(case
when c1 > c2 then
c1 || ',' || c2
else
c2 || ',' || c1
end) flag
from t1 a) v1
group by flag
having count(*) >= 5
select flag, count(*)
from (select c1,
c2,
(case
when c1 > c2 then
c1
else
c2
end) || ',' || (case
when c1 > c2 then
c2
else
c1
end) flag
from t1 a) v1
group by flag
having count(*) >= 5
with c as (select cardno1 , cardno2,count(1) cnt from trans group by cardno1 , cardno2 )
select c1.cardno1,c1.cardno2,c1.cnt +c2.cnt cnt
from c c1,c c2
where c1.cardno1=c2.cardno2
and c1.cardno2 = c2.cardno1
and c1.cnt+c2.cnt > 10;
这个感觉有点问题,要包含转和被转
楼主是想要的相互间有转账的吧?单个转的超过11次的应该不要吧?
with c as (select cardno1 , cardno2,count(1) cnt from trans group by cardno1 , cardno2 )
select c1.cardno1,c1.cardno2,c1.cnt +c2.cnt cnt
from c c1,c c2
where c1.cardno1=c2.cardno2
and c1.cardno2 = c2.cardno1
and c1.cnt+c2.cnt > 10;
应该这样比较合适
select c1,c2,count(*)
from(select cardno1 as c1, cardno2 as c2 from trans
union all
select cardno2, cardno1 from trans
)
group by c1,c2
having count(*) > 10;
--测试数据
create table t1 (c1 varchar2(10),c2 varchar2(10))
insert into t1 values ('A','B');
insert into t1 values ('A','B');
insert into t1 values ('A','B');
insert into t1 values ('A','B');
insert into t1 values ('B','A');
insert into t1 values ('B','A');
insert into t1 values ('B','A');
--查询记录
select count(*)
from t1
where exists ( select * from t1 t2 where t1.c1=t2.c2 and t1.c2=t2.c1)