mysql - 如何解决子查询多选返回多于1行错误

Question

我有一个很长的 MYSQL 查询，它从四个表中选择总数，但其中一个子查询返回多于一行并导致错误。
这是我的查询:

select created,  
    (select coalesce(sum(equal_to_dollar), 0) from capitals group by created )  as capital,

    (select coalesce(sum(incoming), 0) - coalesce(sum(outgoing), 0) from transactions where currency = 'دالر') +
    (select coalesce(sum(equal_to_dollar), 0) from transactions where incoming != 0 ) -
    (select coalesce(sum(equal_to_dollar), 0) from transactions where outgoing != 0 ) as total_transaction,

    (select coalesce(sum(incoming), 0) - coalesce(sum(outgoing), 0) from temporary_clients where currency = 'دالر') +
    (select coalesce(sum(equal_to_dollar), 0) from temporary_clients where incoming != 0 ) -
    (select coalesce(sum(equal_to_dollar), 0) from temporary_clients where outgoing != 0 ) as total_temp_client,

    (select coalesce(sum(outgoing), 0) - coalesce(sum(incoming), 0) from money_transmission where currency = 'دالر') +
    (select coalesce(sum(equal_to_dollar), 0) from money_transmission where outgoing != 0 ) -
    (select coalesce(sum(equal_to_dollar), 0) from money_transmission where incoming != 0 ) as total_transmission,

    (select coalesce(total_transaction + total_temp_client + total_transmission, 0)) as total,

    (select capital - abs(coalesce(total_transaction + total_temp_client + total_transmission, 0))) as result
    from capitals group by created

在此查询中，第一个选择created返回多个值，并且以大写结尾的第一个子查询也返回多个值。
此查询应返回数据库中每个日期的资本与其他三个表总计的计算结果。
我检查了许多具有相同错误的问题，并且所有问题都在 where 子句中使用了 IN 或 inner join 但我认为我的情况有所不同，我不知道该怎么办。
如果您知道如何解决这个问题，请帮助我。
谢谢你:)

Answer 1

尝试如下:

select created,c.sum_dollars

    (select coalesce(sum(incoming), 0) - coalesce(sum(outgoing), 0) from transactions where currency = 'دالر') +
    (select coalesce(sum(equal_to_dollar), 0) from transactions where incoming != 0 ) -
    (select coalesce(sum(equal_to_dollar), 0) from transactions where outgoing != 0 ) as total_transaction,

    (select coalesce(sum(incoming), 0) - coalesce(sum(outgoing), 0) from temporary_clients where currency = 'دالر') +
    (select coalesce(sum(equal_to_dollar), 0) from temporary_clients where incoming != 0 ) -
    (select coalesce(sum(equal_to_dollar), 0) from temporary_clients where outgoing != 0 ) as total_temp_client,

    (select coalesce(sum(outgoing), 0) - coalesce(sum(incoming), 0) from money_transmission where currency = 'دالر') +
    (select coalesce(sum(equal_to_dollar), 0) from money_transmission where outgoing != 0 ) -
    (select coalesce(sum(equal_to_dollar), 0) from money_transmission where incoming != 0 ) as total_transmission,

    (select coalesce(total_transaction + total_temp_client + total_transmission, 0)) as total,

    (select capital - abs(coalesce(total_transaction + total_temp_client + total_transmission, 0))) as result
    from capitals
 JOIN (select coalesce(sum(equal_to_dollar), 0) as sum_dollars, created from capitals group by   created )  c ON capitals.created = c.created
 group by created

我无法真正测试，因为我不知道你的数据库架构。无论如何，您应该加入这些结果集。