假设有一个包含两个表的数据库,即Loans和Billpayment。
对于Loans表,我有以下列:
loanId,
clientId(primary key),
DisbursementDate,
applicationDate,
approvalDate,
loanNumber,
loanAmount,
interestRate
Billpayment表列包括:
billId(primary),
customerId(foreign),
billAmount,
payment_status[success/fail],
billDate
我如何才能获得2018年3月已支付账单的客户,并显示在2018年剩余几个月内至少支付了一笔账单的客户数量,除此之外,还要显示账单支付客户在2018年内是否有贷款?
最佳答案
好吧,让我们把这件事做完。我没有测试过这些,也没有对它进行适当的优化,但希望它能有所帮助。
要列出2018年3月的付款,假设我们不在乎是否成功,请执行以下操作:
select * from billpayment
where month(billDate) = 3 and year(billDate) = 2018
若要将相关客户信息包含在上述内容中,请加入:
select * from billpayment
join loans on customerId = clientId
where month(billDate) = 3 and year(billDate) = 2018
若要仅列出在2018年另一个月内也付款的客户,请再次加入派生表:
select * from billpayment b
join loans l on b.customerId = l.clientId
join (select distinct b1.clientId as clientid1 from billpayment b1
where year(b1.billdate) = 2018 and month(b1.billdate) <> 3) c
on b.customerId = c.clientId1
where month(b.billDate) = 3 and year(b.billDate) = 2018
要检查他们是否在2018年开始贷款(假设按申请日期),请在where子句中添加and:
select * from billpayment b
join loans l on b.customerId = l.clientId
join (select distinct b1.clientId as clientid1 from billpayment b1
where year(b1.billdate) = 2018 and month(b1.billdate) <> 3) c
on b.customerId = c.clientId1
where month(b.billDate) = 3 and year(b.billDate) = 2018
and year(l.applicationDate) = 2018
关于mysql - 谷歌大查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/54912789/