求助SQL语句高手

dynag 2017-10-20 08:45:55

现在有个业务流水表（trans）大概有百万条数据，里面有部分是转账记录，转出卡号字段为cardno1，转入卡号字段为cardno2，现在需查出里面相互转账超过10笔的记录。例如：A转给B有5笔记录，B转给A有6笔记录，这样相互间的转账记录就有11条。我需要查出所有这样的记录，不通过编程直接一个SQL语句能查出吗？请高手给出SQL语句。

...全文

671 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

wildwolv 2018-08-01

打赏
举报

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

qq_27511267 2018-07-03

打赏
举报

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

--陈飞最棒

nayi_224 2018-06-27

打赏
举报

select flag, count(*)
from (select c1,
c2,
greatest(c1, c2) || least(c1, c2) flag
from t1 a) v1
group by flag
having count(*) >= 5;

nayi_224 2018-06-27

打赏
举报

引用 8 楼 u011512492 的回复:

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

还可以再省略一步

bigdata-sb 2018-06-26

打赏
举报

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

bigdata-sb 2018-06-26

打赏
举报

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

sz_jack 2017-11-18

打赏
举报

看错了，2/3楼都对了吧，你看看那个性能比较优！

sz_jack 2017-11-18

打赏
举报

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; 这个感觉有点问题，要包含转和被转

「已注销」 2017-10-21

打赏
举报

楼主是想要的相互间有转账的吧？单个转的超过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; 应该这样比较合适

碧水幽幽泉 2017-10-20

打赏
举报

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;

junes06 2017-10-20

打赏
举报

--测试数据 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)

dynag 2017-10-20