gpt4 book ai didi

mysql - 获取与另一个表不匹配的行

转载 作者:行者123 更新时间:2023-11-29 11:28:12 25 4
gpt4 key购买 nike

我有一个工作查询,它​​按照我喜欢的方式生成数据,但现在我希望它添加另一个子句语句,该语句将从 tbl_loan_master 获取不在 tbl_loanledger 上的行.

示例数据:

tbl_借款人

------------------------------------------
| id | first_name | last_name | deleted |
| 1 | Joe | Smith | 0 |
| 2 | Lily | Mag | 0 |
| 3 | Zen | Green | 0 |
| 4 | Kim | Chan | 0 |
| 5 | Bob | Mac | 1 |
| 6 | Ben | Cork | 0 |
------------------------------------------

tbl_loan_master

----------------------------------------------------------------------
| id | borrowers_id | loan | date_created | due_date | deleted |
| 1 | 4 | 300 | 2016/04/28 | 2017/04/28 | 0 |
| 2 | 1 | 100 | 2016/05/05 | 2017/05/05 | 0 |
| 3 | 2 | 500 | 2016/06/08 | 2017/06/08 | 0 |
| 4 | 1 | 200 | 2016/06/13 | 2017/06/13 | 0 |
| 5 | 3 | 150 | 2016/06/15 | 2017/06/15 | 0 |
| 6 | 6 | 50 | 2016/06/16 | 2017/06/16 | 0 |
----------------------------------------------------------------------

tbl_loanledger

------------------------------------------------------------------------------
| id | borrowers_id | loanmaster_id | payment | balance| date_created | deleted
| 1 | 4 | 1 | 50 | 250 | 2016/05/28 | 0
| 2 | 1 | 2 | 20 | 80 | 2016/05/25 | 0
| 3 | 1 | 2 | 30 | 50 | 2016/06/01 | 0
| 4 | 2 | 3 | 100 | 400 | 2016/06/09 | 0
| 5 | 2 | 3 | 50 | 350 | 2016/06/10 | 0
| 6 | 3 | 4 | 50 | 150 | 2016/06/16 | 0
------------------------------------------------------------------------------

这是工作查询:

$query = "SELECT `tbl_borrowers`.* , `tbl_loanledger`.*, `tbl_loan_master`.*
FROM `tbl_borrowers`
LEFT JOIN `tbl_loanledger`
ON `tbl_borrowers`.id = `tbl_loanledger`.borrower_id
LEFT JOIN `tbl_loan_master`
ON `tbl_loan_master`.id = `tbl_loanledger`.loanmaster_id
WHERE `tbl_borrowers`.deleted = 0 AND `tbl_loanledger`.deleted = 0 AND MONTH ( `tbl_loanledger`.date_created) = MONTH(CURRENT_DATE)
GROUP BY `tbl_loanledger`.borrower_id
ORDER BY `tbl_borrowers`.last_name";

预期结果将在贷款分类账和贷款账户中输出借款人当月(六月)的最后一笔交易。就像 Ben Cork 不在贷款分类账上一样,他在贷款账户上,但我想将他输出到结果集上。已删除的列表示如果为 0 则表示该列处于事件状态,如果为 0 则表示已被删除。

预期结果:

|First Name | Last Name |   Due Date   |    Balance     |
| Ben | Cork | 2017/06/16 | 50 |
| Joe | Smith | 2017/06/13 | 50 |
| Lily | Mag | 2017/06/08 | 350 |
| Zen | Green | 2017/06/15 | 150 |

最佳答案

尝试以下操作;)

select 
tb.first_name, tb.last_name, coalesce(tlm.Loan, 0) as Loan, coalesce(t.`Amount Paid`, 0) as `Last Amount Paid`
from tbl_borrowers tb
left join tbl_loan_master tlm
on tb.id = tlm.borrowers_id
left join (
select t1.*
from tbl_loanledger t1
inner join (
select max(id) as id
from tbl_loanledger
group by borrowers_id, loanmaster_id
) t2 on t1.id = t2.id
) t
on tb.id = t.borrowers_id
and tlm.id = t.loanmaster_id

SQLFiddle DEMO HERE

关于mysql - 获取与另一个表不匹配的行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/37849345/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com