現在有個業務流水表（trans）大概有百萬條資料，里面有部分是轉賬記錄，轉出卡號欄位為cardno1，轉入卡號欄位為cardno2，現在需查出里面相互轉賬超過10筆的記錄。例如：A轉給B有5筆記錄，B轉給A有6筆記錄，這樣相互間的轉賬記錄就有11條。我需要查出所有這樣的記錄，不通過編程直接一個SQL陳述句能查出嗎？請高手給出SQL陳述句。
uj5u.com熱心網友回復：
頂頂頂頂頂頂uj5u.com熱心網友回復：
--測驗資料
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)
uj5u.com熱心網友回復：
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;
uj5u.com熱心網友回復：
樓主是想要的相互間有轉賬的吧？單個轉的超過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;
應該這樣比較合適
uj5u.com熱心網友回復：
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;
這個感覺有點問題，要包含轉和被轉
uj5u.com熱心網友回復：
看錯了，2/3樓都對了吧，你看看那個性能比較優！uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
還可以再省略一步
uj5u.com熱心網友回復：
select flag, count(*)from (select c1,
c2,
greatest(c1, c2) || least(c1, c2) flag
from t1 a) v1
group by flag
having count(*) >= 5;
uj5u.com熱心網友回復：
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
--陳飛最棒
uj5u.com熱心網友回復：
select t1.cardno1,t1.cardno2,count(t1.*) from trans t1,trans t2 where t1.cardno1=t2.cardno2 and t1.cardno2=t2.cardno1group by t1.cardno1,t1.cardno2
having count(t1.*) >= 10
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/62110.html
標籤：開發
下一篇：oracle 資料段重復問題？