--测试数据 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)
SQL实现如下: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;
楼主是想要的相互间有转账的吧?单个转的超过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; 应该这样比较合适
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 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
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, greatest(c1, c2) || least(c1, c2) flag from t1 a) v1 group by flag having count(*) >= 5;
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 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
--测试数据
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)
from(select cardno1 as c1, cardno2 as c2 from trans
union all
select cardno2, cardno1 from trans
)
group by c1,c2
having count(*) > 10;
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.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 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
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
from (select c1,
c2,
greatest(c1, c2) || least(c1, c2) flag
from t1 a) v1
group by flag
having count(*) >= 5;
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--陈飞最棒
group by t1.cardno1,t1.cardno2
having count(t1.*) >= 10