mysql - union 在表中返回 2 行而不是 1 行

Question

我的数据库中有这个表，我正在尝试获取 2 组值并将它们放在一个联合中。这是表格。 我的联盟基本上有两个术语，其中 interest_payment > 0 和 interest_paid > 0 ，如果 interest_payment 大于零，它将获取该行并将其添加到下一行，如果下一行的 interest_payment > 0 则将其添加到该行，但如果 interest_payment < 0 ，它应该在同一行中添加 interest_paid 。但是现在，当我运行查询时，我得到了它。基本上 netInterest 不应该显示 Null，因为我使用的是总和，所以它应该只返回一行。

这是所有这些的 sql。

Select sum(investedMoney) as investMoney , sum(estimatedEarning) as estimatedEarning , sum(NetInterest) as NetInterest, Eurosymbol
                from
                (
                select  sum(round(a.Amount * e.average_rate / f.average_rate, 2)) as investedMoney ,
                        sum(round((d.interest_payment + d.overdue_payment) * e.average_rate / f.average_rate, 2)) as estimatedEarning,
                        0 as NetInterest,
                        c.symbol as Eurosymbol
                from   investment a
                inner join money_offer b
                on a.ORIG_ID = b.investment_orig_id and b.UPDATE_DT is null
                inner   join payment_plan d 
                on d.offer_orig_id = b.ORIG_ID and d.interest_payment > 0 and d.UPDATE_DT is null
                inner   join currency c 
                        on  d.currency = c.ID 
                inner join exchange_rates e 
                        on  e.currency_id = a.Currency
                inner join exchange_rates f 
                        on  f.currency_id = a.Currency
                where   a.Owner = 533  and
                                a.UPDATE_DT is null

                        union 


                select  0 as investedMoney ,
                        0 as estimatedEarning,
                        sum(round((d.interest_paid+ d.overdue_paid) * e.average_rate / f.average_rate, 2)) as NetInterest,
                        c.symbol as Eurosymbol
                from   investment a
                inner join money_offer b
                on a.ORIG_ID = b.investment_orig_id and b.UPDATE_DT is null
                inner   join payment_plan d 
                on d.offer_orig_id = b.ORIG_ID and d.interest_paid > 0 and d.UPDATE_DT is null
                inner   join currency c 
                        on  d.currency = c.ID 
                inner join exchange_rates e 
                        on  e.currency_id = a.Currency
                inner join exchange_rates f 
                        on  f.currency_id = a.Currency
                where   a.Owner = 533  and
                                a.UPDATE_DT is null
                )tmptbl
                group by Eurosymbol;

Answer 1

在 d.interest_payment > 0 或 d.interest_paid > 0 的情况下，您在 payment_plan 表中存储不同的货币。我的猜测是你有虚拟 ID 来表示“没有货币”。假设您在 currency.symbol 中为该特定虚拟货币存储了一个 NULL 值，您可以在整个查询中只取它的 max。

此外，我认为出于您的目的，您应该将 union 查询合并为一个。这样您就可以处理同一条记录中两个条件都为真的情况:它们将不会被复制。可以使用 case when 构造来区分这两种图形，如下所示:

  select      sum(case when d.interest_payment > 0 
                    then round(a.Amount * e.average_rate / f.average_rate, 2) 
                    else 0 
                  end) as investedMoney,
              sum(case when d.interest_payment > 0 
                    then round((d.interest_payment + d.overdue_payment) 
                               * e.average_rate / f.average_rate, 2)
                    else 0 
                  end) as estimatedEarning,
              sum(case when d.interest_paid > 0 
                    then round((d.interest_paid + d.overdue_paid) 
                               * e.average_rate / f.average_rate, 2)
                    else 0
                  end) as NetInterest,
              max(c.symbol) as Eurosymbol
  from        investment a
  inner join  money_offer b
          on  a.ORIG_ID = b.investment_orig_id and b.UPDATE_DT is null
  inner join  payment_plan d 
          on  d.offer_orig_id = b.ORIG_ID and d.UPDATE_DT is null
          and (d.interest_payment > 0 or d.interest_paid > 0)
  inner join  currency c 
          on  d.currency = c.ID 
  inner join  exchange_rates e 
          on  e.currency_id = a.Currency
  inner join  exchange_rates f 
          on  f.currency_id = a.Currency
  where       a.Owner = 533  
      and     a.UPDATE_DT is null

您还应该回顾一下为什么您从 exchange_rates(如 e 和 f)加入完全相同的记录两次，这使得表达式 e.average_rate/f.average_rate 始终评估为 1(假设比率永远不会为零)。