现有两张表:
tableA,tableB,它们分别都有两个字段:bank_account(银行账号),amount(金额).现在需要用连接查询它们银行账号相同,金额相等且只有一条相等记录的SQL(也就是说查出来的结果:tableA跟tableB是一对一的关系,不能是一对多,多对一或者多对多的关系),如以下情况就不对,因为怎样连接查询出来会有2条数据。 tableA
bank_account amount
0001 2000
0001 2000 tableB
bank_account amount
0001 2000
谢谢各位。
tableA,tableB,它们分别都有两个字段:bank_account(银行账号),amount(金额).现在需要用连接查询它们银行账号相同,金额相等且只有一条相等记录的SQL(也就是说查出来的结果:tableA跟tableB是一对一的关系,不能是一对多,多对一或者多对多的关系),如以下情况就不对,因为怎样连接查询出来会有2条数据。 tableA
bank_account amount
0001 2000
0001 2000 tableB
bank_account amount
0001 2000
谢谢各位。
where a.bank_account=b.bank_account and a.amount=b.amount
SQL> with tableA as
2 (
3 select '0001' bank_account, 2000 amount from dual union all
4 select '0002' bank_account, 2100 amount from dual union all
5 select '0001' bank_account, 2000 amount from dual
6 )
7 ,tableB as
8 (
9 select '0001' bank_account, 2000 amount from dual union all
10 select '0002' bank_account, 2100 amount from dual
11 )
12 select a.bank_account,a.amount
13 from (select bank_account, amount, count(*) cn
14 from tableA
15 group by bank_account, amount) a,
16 (select bank_account, amount, count(*) cn
17 from tableB
18 group by bank_account, amount) b
19 where a.bank_account = b.bank_account
20 and a.amount = b.amount
21 and a.cn = 1
22 and b.cn = 1
23 ;BANK_ACCOUNT AMOUNT
------------ ----------
0002 2100SQL>
from (select count(a.b) cnt, a.bank_account bank_account, a.amount amount
from tablea a, tableb b
where a.bank_account = b.bank_account
and a.amount = b.amount
group by a.bank_account, b.bank_account,a.amount,b.amount) SSS
where SSS.cnt = 1
SELECT bank_account, amount
FROM (SELECT A.bank_account, A.amount, COUNT(1) COU
FROM tableA A, tableB B
WHERE A.bank_account = B.bank_account
AND A.amount = B.amount
GROUP BY A.bank_account, A.amount)
WHERE COU = 1
select a.bank_account,a.amount
from (select bank_account,amount,
row_number() over(partition by bank_account,amount order by bank_account,amount) rn
from tba) a,
(select bank_account,amount,
row_number() over(partition by bank_account,amount order by bank_account,amount) rn
from tbb) b
where a.bank_account=b.bank_account and a.amount=b.amount and
a.rn=1 and b.rn=1--orselect a.bank_account,a.amount
from (select bank_account,amount
from tba
group by bank_account,amount) a,
(select bank_account,amount
from tbb
group by bank_account,amount) b
where a.bank_account=b.bank_account and a.amount=b.amount
from
(select bank_account amount
from tableA
group by bank_account amount having count(*) = 1 ) a
,(select bank_account amount
from tableA
group by bank_account amount having count(*) = 1 ) b
where a.bank_account = b.bank_account and a.amount = b.amount