mysql - 关系代数银行数据库

Question

我有一个银行数据库的关系模式

Customer(custid PK, name, city, streetaddr, province)

Account(acctid PK, custid, atype, startdate, balance, branchid), (custid ref Customer, branchid ref Branch)

Branch(branchid PK, mgrid, city, streetaddr, province), (mgrid ref Employees.empid)

Employees(empid PK, name, branchid, salary, city, streetaddr, province), (branchid ref Branch)

Transactions(tid PK, acctid, transtype, transdate, transamount, branchid) (acctid ref Account, branchid ref Branch)

I'm trying to find all "savings" account ids, name of the customer, and customer ids, of customers who only made one transaction this year

显然，我需要使用“交易”、“帐户”和“客户”表。我加入了“交易账户”以查看已进行一笔或多笔交易的所有账户，但我在查找“只有一笔”交易的规范时遇到了麻烦。我觉得我需要使用设置差异，但我仍然无法想清楚。

Answer 1

可能是这样的:

select a.acctid, c.custid, c.name
from account a
inner join (
    select acctid
    from transactions
    where year(transdate) = year(curdate())
    group by acctid
    having count(tid) = 1
) t on a.acctid = t.acctid
inner join customer c
on a.custid = c.custid
where a.atype = 'savings';