我在sql select查询中记录错误
我在sql select查询中记录错误
提问于 2016-06-13 08:21:37
我想从MySQL中选择数据。还想在查询执行时做一些计算。我有两个SQL表：
贷款表:
loan_id|Customer_name|Total_amount|Collector
1000 |Sashika |55000 |Kapoor
1001 |Amell |11000 |Kapoor
1002 |Oliver |15000 |Kapoor结算表:
Sett_id|Loan_id|Amount
a123 |1000 |1000
b123 |1000 |1000特别是我想指出的是，1001 & 1002贷款还没有收到任何结算。，我想按收集人的名字选择贷款。选择应该是这样的：
输出表
Loan_ID| Customer_Name|Total_Amount|Total_Received|Total_Due
1000 |Sashika |55000 |2000 |53000
1001 |Amell |11000 |0 |11000
1002 |Oliver |15000 |0 |15000要做到这一点，我使用下面的选择查询。但它只显示1000(loan_id)记录。我想是因为只有1000人(Loan_id)有结算记录。1001和1002没有任何记录，这就是为什么它没有显示。但我的要求是加载所有贷款细节，如上面的输出表。
目前，我使用以下代码：
select
loan.loan_id as 'Loan ID'
,loan.customer_name as 'Customer Name'
,loan.total_amount as 'Total Amount'
,ifnull(sum(settlement.amount),0) as 'Total Received'
,((loan.total_amount)-ifnull(sum(settlement.amount), 0))as 'Total Due'
from loan
inner join settlement on loan.loan_id = settlement.loan_id
where loan.collector_name='kapoor'
group by loan.loan_id就像我说的，它没有显示1001,1002 (loan_id)记录，比如Output Table
回答 3
Stack Overflow用户
发布于 2016-06-13 08:29:44
在本例中，上述两个表之间需要左联接。
select
loan.loan_id AS 'Loan ID',
loan.customer_name AS 'Customer Name',
loan.total_amount AS 'Total Amount',
ifnull(sum(settlement.amount),0) AS 'Total Received',
((loan.total_amount)-ifnull(sum(settlement.amount), 0))as 'Total Due'
FROM
loan
LEFT JOIN settlement ON loan.loan_id = settlement.loan_id
WHERE
loan.collector_name = 'kapoor'
GROUP BY loan.loan_idStack Overflow用户
发布于 2016-06-13 08:29:21
INNER JOIN永远不会显示在结算表中找不到的loan_ids。使用LEFT JOIN
Stack Overflow用户
发布于 2016-06-13 08:52:02
您需要起诉一个LEFT JOIN来返回您想要的数据，因为结算表中不存在loan_id的1001和1002。
另外，在表结构中，在当前代码中使用collector，在下面的代码中使用collector_name，在SQL示例中使用collector。
SELECT
l.loan_id AS 'Loan ID',
l.customer_name AS 'Customer Name',
l.total_amount AS 'Total Amount',
IFNULL(SUM(s.amount),0) AS 'Total Received',
((l.total_amount) - IFNULL(SUM(s.amount), 0)) AS 'Total Due'
FROM loan l
LEFT JOIN settlement s on l.loan_id = s.loan_id
WHERE l.collector_name = 'kapoor'
GROUP BY l.loan_id输出：
loan_id Customer_name Total_amount Total Received Total Due
1000 Sashika 55000 2000 53000
1001 Amell 11000 0 11000
1002 Oliver 15000 0 15000SQL：http://sqlfiddle.com/#!9/fe704/2/0
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/37784860
复制相关文章
相似问题
腾讯云开发者
