Now has A business flow meter (trans) there are about one million data, there are partly transfer record, turn out the card number field for cardno1, transferred to the card number field is cardno2, now need to find out the inside mutual transfer of more than 10 records, for example: five pen records A to B, B to A six pen records, such mutual transfer record have 11, I need to find out all such records, not directly by programming A SQL statement can you find out? Please give the SQL statement,

CodePudding user response:

After another wall bumps bumps

CodePudding user response:

- test data
The 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');



- query record
Select count (*)
The from t1
Where the exists (select * from t1 to t2 where t1. The c1=t2. C2 and t1. The c2=t2. The c1)

CodePudding user response:

SQL implementation is as follows:

 
Select c1 and c2, count (*) 
The from (select cardno1 as c1, cardno2 as c2 from trans 
Union all 
The select cardno2, cardno1 from trans 
) 
Group by c1 and c2 
Having the count (*) & gt; 10; 

CodePudding user response:

The original poster is want to each other have a transfer? Not more than 11 times should be turning a single?
With c as (select cardno1 cardno2, count (1) the from CNT trans group by cardno1, cardno2)
Select c1 cardno1, c1 cardno2, c1. The CNT + c2. The CNT CNT
The from c c1, c2 c
Where c1. Cardno1=c2. Cardno2
And c1. Cardno2=c2. Cardno1
And c1. CNT + c2. CNT & gt; 10;
Should be more appropriate

CodePudding user response:

With c as (select cardno1 cardno2, count (1) the from CNT trans group by cardno1, cardno2)
Select c1 cardno1, c1 cardno2, c1. The CNT + c2. The CNT CNT
The from c c1, c2 c
Where c1. Cardno1=c2. Cardno2
And c1. Cardno2=c2. Cardno1
And c1. CNT + c2. CNT & gt; 10;
This was a little problem, turn to include and transferred

CodePudding user response:

Wrong, two-thirds of the floor to the right, you look at the performance comparison is excellent!

CodePudding user response:

Select the flag, the count (*)
The from (select c1,
C2,
(case
The when c1 & gt; C2 then
C1
The else
C2
End) | | ', '| | (case
The when c1 & gt; C2 then
C2
The else
C1
End) flag
The from t1 v1
a)Group by flag
Having the count (*) & gt;=5

CodePudding user response:

Select the flag, the count (*)
The from (select c1,
C2,
(case
The when c1 & gt; C2 then
C1 | | ', '| | c2
The else
C2 | | ', '| | c1
End) flag
The from t1 v1
a)Group by flag
Having the count (*) & gt;=5

CodePudding user response:

refer to the eighth floor u011512492 response:

select flag, count (*)
The from (select c1,
C2,
(case
The when c1 & gt; C2 then
C1 | | ', '| | c2
The else
C2 | | ', '| | c1
End) flag
The from t1 v1
a)Group by flag
Having the count (*) & gt;=5

You can also omit the step again

CodePudding user response:

Select the flag, the count (*)
The from (select c1,
C2,
The greatest (c1, c2) | | further (c1, c2) flag
The from t1 v1
a)Group by flag
Having the count (*) & gt;=5;

CodePudding user response:

Select the flag, the count (*)
The from (select c1,
C2,
(case
The when c1 & lt; C2 then
C1 | | ', '| | c2
The else
C2 | | ', '| | c1
End) flag
The from t1 v1
a)Group by flag
Having the count (*) & gt;=10



Best - Chen

CodePudding user response:

The 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 a count (t1) *) & gt;=10