sql语句判断对错
模式图：
要求：查找在 Brooklyn的所有支行都有存款的客户
请判断以下两个答案的对错，并分析（最好能用实际的操作检验，我没有环境:P）：
select distinct S.customer_name
from depositor S
where not exists (
(select *
from branch B
where branch_city = 'Brooklyn'and not exists
(select *
from depositor T, account R
where T.account_number = R.account_number and
S.customer_name = T.customer_name and
R.branch_name = B.branch_name)))
select distinct S.customer_name
from depositor S
where not exists (
(select *
from branch B
where branch_city = 'Brooklyn'and not exists
(select *
from account R
where S.account_number = R.account_number and
R.branch_name = B.branch_name)))
还有另一种思路,判断客户拥有的账户的支行集合是否包含位于Brooklyn的所有支行的集合，：
select distinct S.customer_name
from depositor S
where not exists (
(select branch_name
from branch
where branch_city = 'Brooklyn')
except
(select R.branch_name
from depositor T, account R
where T.account_number = R.account_number and
S.customer_name = T.customer_name ))
写得也是啰嗦了一点，改一下：
我能注册我自豪
10 years, 6 months ago
Answers
试了一下, 两个sql都可以. 第一个sql写错了一处, 把T.branch_name = B.branch_name改为R.branch_name = B.branch_name
另一个思路, 比较 某客户 (在Brooklyn有存款的支行数)=(Brooklyn的支行数)
select TMP.cname from
(select D.customer_name cname, count(distinct A.branch_name) count
from depositor D, account A, Branch B
where A.account_number = D.account_number and
A.branch_name = B.branch_name and
B.branch_city='Brooklyn'
group by D.customer_name
) TMP,
(select count(branch_name) count from branch
where branch_city='Brooklyn'
) B1
where TMP.count=B1.count;
tsugumi
answered 10 years, 6 months ago
