php - 如何在使用 LEFT JOIN 和 GROUP BY 时显示不同列的总值

Question

我在一个传销网站上工作。它有一个名为 my payouts 的页面，其中有两种类型的支出表 ，即silver_payout、golden_payout 第三个是Total Payout，是两者相加。我想按日期显示支出。这两个表都包含 date 列。

白银支付表

sp_id | username |    name      | sp_income | admin_charge | tds | net_payout |    date
      |          |              |           |              |     |            |
  1   |  super   | Super Admin  |   750     |      75      | 38  |   637      |  2019-10-03
      |          |              |           |              |     |            |
  2   |  super   | Super Admin  |   750     |      75      | 38  |   637      |  2019-10-03

黄金支出表

gp_id | username |    name      | gp_income | admin_charge | tds | net_payout |    date
      |          |              |           |              |     |            |
  1   |  super   | Super Admin  |   750     |      75      | 38  |   637      |  2019-10-03
      |          |              |           |              |     |            |
  2   |  super   | Super Admin  |   750     |      75      | 38  |   637      |  2019-10-03

这是我期望的总支出

 id   | username | silver pay   | golden pay| total pay   | admin_charge| tds | net_payout |    date
      |          |              |           |             |             |     |            |
  1   |  super   | 1500         |   1500    |    3000     |    300      | 150 |  2550      |  2019-10-03

这就是我得到的总支出

 id   | username | silver pay   | golden pay| total pay   | admin_charge| tds | net_payout |    date
      |          |              |           |             |             |     |            |
  1   |  super   | 3000         |   3000    |    6000     |    600      | 304 |  5096      |  2019-10-03

SELECT silver_payout.username, 
    COUNT(silver_payout.sp_id) AS id,
    SUM(silver_payout.sp_income) AS income,
    SUM(silver_payout.admin_charge) AS ach,
    SUM(silver_payout.tds) AS theTDS,
    SUM(silver_payout.net_payout) AS np,
    silver_payout.date AS sdate,
    golden_payout.username,
    COUNT(golden_payout.gp_id) AS gid, 
    SUM(golden_payout.gp_income) AS gold_income, 
    SUM(golden_payout.admin_charge) AS gach,
    SUM(golden_payout.tds) AS gtheTDS,
    SUM(golden_payout.nett_payout) AS gnp, 
    golden_payout.date AS gdate
FROM (silver_payout 
LEFT JOIN golden_payout
ON silver_payout.username = golden_payout.username)
WHERE silver_payout.username = '$search'
GROUP BY STR_TO_DATE(sdate,'%Y-%m-%d'), STR_TO_DATE(gdate,'%Y-%m-%d')

如有任何帮助，我们将不胜感激。

Answer 1

使用union all 和group by:

select username, name,
       sum(income * is_silver) as s_income,
       sum(income * is_gold) as g_income,
       sum(income) as total_income,
       . . .  -- continue for the rest of the columns
from ((select s.*, 1 as is_silver, 0 as is_gold
       from silver s
      ) union all
      (select g.*, 0, 1
       from gold
      )
     ) sg
where date = ?
group by username, name