代码之家 › 专栏 › 技术社区 › Dave Chambers

在MySQL中,如何使用联接表中的一行筛选出组CONCAT中不需要的日期?

outer-join inner-join left-join mysql

Dave Chambers · 技术社区 · 6 年前

我有一个 loan 桌子和 loan_status 桌子。这个 贷款 在其他列中,表包括 loan_id 以及 date_issued 列。这里我有一个查询 签发日期 对于其他贷款:

(select GROUP_CONCAT(Date(lj.date_issued) SEPARATOR ', ') from loan lj INNER JOIN client clj ON (clj.client_id = lj.client_id) 

where clj.client_id = '2299629' and lj.id != l.id) as other_loan_issue_dates,

因为这个客户有7笔贷款,所以我有一张这样的表,上面有6个逗号分隔的日期:

问题是:

我需要排除 other_loan_issue_dates 任何 贷款 有一个 status_id 属于 27 在 贷款状况 桌子和那个 状态id 是 贷款状况 那张桌子 贷款 . 每笔贷款都有许多状态。

下面是一个例子:

此查询显示 test 列,该列确定是否应在 其他贷款发放日期 :

select status_id = 27 as test from loan_status ls where loan_id = 2052456 ORDER BY datestamp DESC LIMIT 1;

以下错误,无法完成运行:

(select GROUP_CONCAT(Date(lj.date_issued) SEPARATOR ', ') from loan lj INNER JOIN client clj ON (clj.client_id = lj.client_id) 

JOIN loan_status ls ON ls.status_id = (
select status_id from loan_status ls where loan_id = 2052456 ORDER BY datestamp DESC LIMIT 1
)

where clj.client_id = '2299629' and lj.id != l.id and ls.status_id != 27) as other_loan_issue_dates,

我也尝试了以下方法,但我不知道它是否有效,因为它似乎会使结果成倍增加:

(select GROUP_CONCAT(Date(lj.date_issued) SEPARATOR ', ') from loan lj INNER JOIN client clj ON (clj.client_id = lj.client_id) 

LEFT OUTER JOIN loan_status ls ON (ls.loan_id = lj.id)

where clj.client_id = '2299629' and lj.id != l.id and (select status_id = 27 as test from loan_status ls where loan_id = ls.loan_id ORDER BY datestamp DESC LIMIT 1) = 0) as other_loan_issue_dates,

我怎么用最后一个 贷款状况 排,看看是不是 状态id 是 27个 如果是,排除 贷款 ?

1 回复 | 直到 6 年前

Ildar Akhmetov 6 年前

我不确定你的查询是否正确,因为有些更大的查询只是其中的一部分。不过,我建议你试试这样的方法:

SELECT GROUP_CONCAT(Date(lj.date_issued) SEPARATOR ', ')
FROM loan lj
INNER JOIN client clj ON (clj.client_id = lj.client_id) 
WHERE clj.client_id = '2299629'
AND lj.id != l.id
AND lj.loan_id NOT IN (SELECT loan_id from loan_status WHERE status_id = 27)

这个 SELECT loan_id from loan_status WHERE status_id = 27 会给你贷款的身份证 status_id=27 . 然后你把 NOT IN 告诉MySQL排除此类贷款。