有四张表,
第一张表是用户信息customer(customer_name,customer_street,customer_city)
第二张表是支行信息branch(branch_name,branch_city)
第三张表是账户信息account(account_name,branch_name,balance)
第四张表是存款信息depositor(account_name,customer_name)
用sql查询在北京所有支行都开过户的用户信息?(branch_city=beijing)
用sql查询在朝阳支行只开过一个账户的用户信息?(branch_name=chaoyang)1.
select * from customer where customer_name in
(
select a.customer_name from customer a,branch b,account c where
a.customer_city=b.branch_city
and b.branch_name=c.branch_name
and b.branch_city='beijing'
group by a.customer_name having count(*)=
(select count(*) from branch where branch_city='beijing')
);2.
select * from customer where customer_name in
(select a.customer_name from customer a,branch b,account c where
a.customer_city=b.branch_city ...
这是在网上找到的答案,但是不正确,自己实在又想不出来(快3个小时了)
只想到了这里:select a_name from depositor D,account A,branch B where B.b_city='武汉' AND B.b_name=A.b_name AND D.a_name=A.a_name; (a_name即account_name,以此类推)
第一张表是用户信息customer(customer_name,customer_street,customer_city)
第二张表是支行信息branch(branch_name,branch_city)
第三张表是账户信息account(account_name,branch_name,balance)
第四张表是存款信息depositor(account_name,customer_name)
用sql查询在北京所有支行都开过户的用户信息?(branch_city=beijing)
用sql查询在朝阳支行只开过一个账户的用户信息?(branch_name=chaoyang)1.
select * from customer where customer_name in
(
select a.customer_name from customer a,branch b,account c where
a.customer_city=b.branch_city
and b.branch_name=c.branch_name
and b.branch_city='beijing'
group by a.customer_name having count(*)=
(select count(*) from branch where branch_city='beijing')
);2.
select * from customer where customer_name in
(select a.customer_name from customer a,branch b,account c where
a.customer_city=b.branch_city ...
这是在网上找到的答案,但是不正确,自己实在又想不出来(快3个小时了)
只想到了这里:select a_name from depositor D,account A,branch B where B.b_city='武汉' AND B.b_name=A.b_name AND D.a_name=A.a_name; (a_name即account_name,以此类推)
解决方案 »
- 关于两台机器的Oracle性能比对问题
- 请问下PL/SQL 里面的 表a.id (+)= 表b.main_id 是什么意思啊
- JSP,ORACLE,批量生成插入号码
- 插入Oracle 表中不能处理&did?????请帮忙解决,谢谢!!!急用!!!!!
- 专门配置一台透明网关服务器连接Sql Server 2K,跟Oracle数据库服务器分开,但是网关服务器重启监听器的时候老是提示“协议适配器错误”
- 新手请教这个视图该怎么写?
- pl/sql 函数问题
- 请问:不安装oracle客户端,怎么运行PB编译好的EXE程序?
- 关于Linux下oracle稳定性与安全性的提问
- 高分求购一个存储过程
- oracle 传统分页数据取出来后卡住的问题
- oracle sql查询问题
select * from customer where customer_name in
(select customer_name from
(select a.customer_name,b.branch_name from customer a,branch b,account c where
a.customer_city=b.branch_city and b.branch_name=c.branch_name and b.branch_city='beijing')
group by customer_name having count(*)=(select count(*) from branch where branch_city='beijing')
);2.
select * from customer where customer_name in
(select customer_name from
(select a.customer_name,c.account_name from customer a,branch b,account c where
a.customer_city=b.branch_city and b.branch_name=c.branch_name and b.branch_name='chaoyang')
group by customer_name having count(*)=1
);
select * from customer where customer_name in
(select customer_name from
(select a.customer_name,c.account_name from customer a,branch b,account c where
a.customer_city=b.branch_city and b.branch_name=c.branch_name and b.branch_name='chaoyang')
group by customer_name having count(*)=1)
);
select c.customer_name,c.customer_street,c.customer_city
from depositor d,customer c,branch b,account a
where b.branch_name=c.branch_name and b.branch_city='beijing' and d.account_name=c.account_name and a.customer_name=d.customer_name;--用sql查询在朝阳支行只开过一个账户的用户信息?(branch_name=chaoyang)
select max(c.customer_name),max(c.customer_street),max(c.customer_city),a.account_name
from depositor d,customer c,branch b,account a
where b.branch_name=c.branch_name and b.branch_city='beijing' and d.account_name=c.account_name and a.customer_name=d.customer_name
group by a.account_name
having count(a.account_name)=1
a.customer_city=b.branch_city这个不对啊,难道不能在外地开户?
你的第一个问题,没有group by啊,也没有having
唉,还是得靠自己啊。你们回答的怎么感觉很马虎啊,这是我自己写 的:
select c.c_name,c.c_street,c.c_city from depositor d,customer c,branch b,account a where b.b_name=a.b_name and a.a_name=d.a_name and b.b_city='beijing' and c.c_name=d.c_name group by c.c_name having count(a.a_name)>=(select count(*) from branch where b_city='beijing'); 用sql查询在朝阳支行只开过一个账户的用户信息?(branch_name=chaoyang)
select * from account a,depositor d,customer c where a.b_name='chaoyang' and a.a_name=d.a_name and d.c_name=c.c_name group by c.c_name having count(*)=1;