有四張表，
第一張表是用戶資訊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,以此類推）
uj5u.com熱心網友回復：
1.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
);
uj5u.com熱心網友回復：
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)
);
uj5u.com熱心網友回復：
--用sql查詢在北京所有支行都開過戶的用戶資訊？（branch_city=beijing）
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
uj5u.com熱心網友回復：
a.customer_city=b.branch_city這個不對啊，難道不能在外地開戶？
uj5u.com熱心網友回復：
你的第一個問題，沒有group by啊，也沒有having
uj5u.com熱心網友回復：
我是提問者，這是我的另一個號，原來那個忘記了。唉，還是得靠自己啊。你們回答的怎么感覺很馬虎啊，
這是我自己寫 的：
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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/116442.html
標籤：基礎和管理