如何在Oracle 11g中使用SQL对象关系语句从三个表中提取数据 [英] How to extract data from three tables using SQL object relational statement in oracle 11g
本文介绍了如何在Oracle 11g中使用SQL对象关系语句从三个表中提取数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有三个表Branch，Account_table和customer.我正在尝试为以下项编写SQL语句:
I have three tables Branch, Account_table and customer. I am trying to write a SQL statement for:
在每个分支机构中，找到储蓄帐户中余额最高的客户.在其当前帐户中显示其名称，余额，分支机构ID和免费透支额.
我创建了三个表并插入数据:
I have created three tables and insert data:
分支表
BID BADDRESS.STREET BADDRESS.CITY BADDRESS.P
---------- -------------------- -------------------- ----------
901 Nicholson Street Edinburgh EH11 5AB
906 East End Garden Glasgow G181QP
912 Fredrick Street London LA112AS
918 Zink Terrace Edinburgh EH149UU
Account_table
ACCNUM ACCTYPE BALANCE BID.BID INRATE LIMITOFFREEOD OPENDATE
------- --------------- ---------- ---------- ---------- ------------- --------
1001 current 820.5 901 .005 800 01-MAY-11
1010 saving 2155 906 .02 0 08-MAR-10
1002 current 2600 912 .005 1000 10-APR-13
1011 saving 4140 918 .02 0 24-OCT-13
客户表
CUSTID CADDRESS.STREET CADDRESS.CITY CADDRESS.POSTCODE CNAME.FIRSTNAME CNAME.SURNAME
---------- -------------------- ----------- -------------------- --------------- -----------
1002 Adam Street Edinburgh EH112LQ Jack Smith
1003 Adam Street Edinburgh EH112LQ Anna Smith
1004 New Tweed Edinburgh EH1158L Liam Bain
1005 Dundas Street Edinburgh EH119MN Usman Afaque
1006 St Andres Square Edinburgh EH12LNM Claire Mackintosh
分支( bID ，街道，城市，p_code，bPhone)
Branch(bID, street, city, p_code, bPhone)
帐户( accNum ，accType，余额， bID ，inRate，limitOfFreeOD，openDate)
Account(accNum, accType, balance, bID, inRate, limitOfFreeOD, openDate)
客户( custID ，街道，城市，邮政编码，标题，名字，姓氏，custHomePhone，custMobile1，custMobile2，niNum)
Customer(custID, street, city, postCode, title, firstName, surName, custHomePhone,custMobile1, custMobile2, niNum)
粗体是主键斜体是外键(在对象关系中，如果我是对的，我们不使用Join).
Bold is primary key Italic is foreign key (In object relational we don't use Join if I am right).
这是我要尝试的操作，但失败了
select c.custid,
(select max(balance) from account_table a
where c.CUSTID = a.bid.bid
and a.acctype='saving' )as highest_saving,
c.cname.firstname,c.CNAME.surname
from customer c;
有帮助吗?谢谢.
推荐答案
帐户表中缺少custID列.我添加了几行数据来创建满足您需求的测试用例.
You are missing custID column in account table. I have added a few more rows of data to create the test case for your requirement.
drop table acct;
drop table branch;
drop table customer;
create table branch(bid number primary key, addr_street varchar2(100), addr_city varchar2(100), addr_p varchar2(20));
insert into branch values(901,'Nicholson Street','Edinburgh','EH11 5AB');
insert into branch values(906,'East End Garden','Glasgow','G181QP');
insert into branch values(912,'Fredrick Street','London','LA112AS');
insert into branch values(918,'Zink Terrace','Edinburgh','EH149UU');
commit;
select * from branch;
输出:
BID ADDR_STREET ADDR_CITY ADDR_P
901 Nicholson Street Edinburgh EH11 5AB
906 East End Garden Glasgow G181QP
912 Fredrick Street London LA112AS
918 Zink Terrace Edinburgh EH149UU
create table customer(custid number primary key, caddr_street varchar2(100), caddr_city varchar2(100),
caddr_p varchar2(10), fname varchar2(100), lname varchar2(100));
insert into customer values(1002,'Adam Street','Edinburgh','EH112LQ','Jack','Smith');
insert into customer values(1003,'Adam Street','Edinburgh','EH112LQ','Anna','Smith');
insert into customer values(1004,'New Tweed','Edinburgh','EH1158L','Liam','Bain');
insert into customer values(1005,'Dundas Street','Edinburgh','EH119MN','Usman','Afaque');
insert into customer values(1006,'St Andres Square','Edinburgh','EH12LNM','Claire','Mackintosh');
commit;
select * from customer;
输出:
CUSTID CADDR_STREET CADDR_CITY CADDR_P FNAME LNAME
1002 Adam Street Edinburgh EH112LQ Jack Smith
1003 Adam Street Edinburgh EH112LQ Anna Smith
1004 New Tweed Edinburgh EH1158L Liam Bain
1005 Dundas Street Edinburgh EH119MN Usman Afaque
1006 St Andres Square Edinburgh EH12LNM Claire Mackintosh
create table acct(accnum number primary key, acctype varchar2(20), balance number, bid number
constraint acct_fk1 references branch(bid),
inrate number, LIMITOFFREEOD number, OPENDATE date, custid number
constraint acct_fk2 references customer(custid));
insert into acct values(1001,'current',820.5,901,0.005,800,to_date('01-MAY-11','dd-mon-yy'),1002);
insert into acct values(1010,'saving',2155,906,0.02,0,to_date('08-MAR-10','dd-mon-yy'),1002);
insert into acct values(1002,'current',2600,912,0.005,1000,to_date('10-APR-13','dd-mon-yy'),1006);
insert into acct values(1011,'saving',4140,918,0.02,0,to_date('24-OCT-13','dd-mon-yy'),1004);
insert into acct values(1012,'saving',4155,906,0.02,0,to_date('08-MAR-10','dd-mon-yy'),1004);
insert into acct values(1013,'current',2600,918,0.005,1000,to_date('10-APR-13','dd-mon-yy'),1004);
commit;
select * from acct;
输出:
ACCNUM ACCTYPE BALANCE BID INRATE LIMITOFFREEOD OPENDATE CUSTID
1001 current 820.5 901 .005 800 01-MAY-11 1002
1010 saving 2155 906 .02 0 08-MAR-10 1002
1002 current 2600 912 .005 1000 10-APR-13 1006
1011 saving 4140 918 .02 0 24-OCT-13 1004
1012 saving 4155 906 .02 0 08-MAR-10 1004
1013 current 2600 918 .005 1000 10-APR-13 1004
select y.fname, y.lname, y.balance, y.bid,ac.accnum,ac.acctype,ac.LIMITOFFREEOD
from (select *
from (select b.bid, c.custid, a.accnum,a.balance,
row_number() over(partition by b.bid order by a.balance desc) rn,
c.fname, c.lname
from acct a
inner join
branch b
on a.bid = b.bid
inner join
customer c
on a.custid = c.custid
where a.acctype = 'saving') x
where x.rn = 1) y
left join
acct ac
on y.custid = ac.custid
and y.bid = ac.bid
and ac.acctype = 'current';
输出:
FNAME LNAME BALANCE BID ACCNUM ACCTYPE LIMITOFFREEOD
Liam Bain 4140 918 1013 current 1000
Liam Bain 4155 906 NULL NULL NULL
这篇关于如何在Oracle 11g中使用SQL对象关系语句从三个表中提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
