MYSQL：从多个表返回多个计数而无需加入

机器人

accounts表有account_id，account_type，date_opened。

loans表有account_id，balance。

选择新贷款的数量：

select count(a.account_id) from account a
    where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH)
    and a.account_type = 'L'

选择未偿还贷款的数量：

select count(*) from loans l 
    where l.balance > 0

要么

SUM(CASE WHEN l.balance > 0 THEN 1 ELSE 0 END) as loans_opened

如何一次选择呢？我尝试过的事情：

select 
    count(a.account_id),
    SUM(CASE WHEN l.balance > 0 THEN 1 ELSE 0 END) as loans_opened
    from account a as new_loans,
    LEFT JOIN loans l ON l.account_id = a.account_id
    where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L'

归还0笔贷款

select 
    count(a.account_id) as new_loans,
    count(l.account_id) as loans_opened
    from account a as new_loans,
    LEFT JOIN loans l ON l.account_id IN (SELECT account_id from account) and balance > 0
    where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L'

查询永不返回

有没有连接的方法？

巴尔玛

您可以通过将两个SELECT查询用作主查询SELECT列表中的子查询来做到这一点：

SELECT (SELECT COUNT(*)
        FROM account
        WHERE date_opened > LAST_DAY(NOW() - INTERVAL 1 MONTH)
        AND account_type = 'L') AS new_loans,
       (SELECT COUNT(*)
        FROM loans
        WHERE balance > 0) AS open_loans;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。