MySql 图形查询多个序列对齐到同一时间 x 轴

Question

我有一些查询要用来制作收入图表。但是现在人们可以从两个不同的来源赚钱，所以我想在同一张图表上将其分成两条线

这个标准收入:

SELECT DATE_FORMAT(earning_created, '%c/%e/%Y') AS day, SUM(earning_amount) AS earning_standard
FROM earnings
WHERE earning_account_id = ? AND earning_referral_id = 0 AND (earning_created > DATE_SUB(now(), INTERVAL 90 DAY))
GROUP BY DATE(earning_created)
ORDER BY earning_created

这是推荐收入:

SELECT DATE_FORMAT(e.earning_created, '%c/%e/%Y') AS day, SUM(e.earning_amount) AS earning_referral
FROM earnings AS e
INNER JOIN referrals AS r
ON r.referral_id = e.earning_referral_id
WHERE e.earning_account_id = ? AND e.earning_referral_id > 0 AND (e.earning_created > DATE_SUB(now(), INTERVAL 90 DAY)) AND r.referral_type = 0
GROUP BY DATE(e.earning_created)
ORDER BY e.earning_created

如何让它一起运行查询，以便它为 y 轴输出两列/系列:earning_standard 和 earning_referral。

但是它们都与 x 轴的相同 day 列/刻度对齐 - 当特定系列没有 yield 时替换为零。

Answer 1

您需要将这两个查询设置为子查询

SELECT DATE_FORMAT(earnings.earning_created, '%c/%e/%Y') AS day, 
       COALESCE(es.earning_standard, 0) AS earning_standard, 
       COALESCE(er.earning_referral, 0) AS earning_referral
FROM earnings
LEFT JOIN (SELECT DATE_FORMAT(earning_created, '%c/%e/%Y') AS day,   
                  SUM(earning_amount) AS earning_standard
           FROM earnings
           WHERE earning_account_id = ? 
           AND earning_referral_id = 0 
           AND (earning_created > DATE_SUB(now(), INTERVAL 90 DAY))
           GROUP BY DATE(earning_created)) AS es 
ON (day = es.day)
LEFT JOIN (SELECT DATE_FORMAT(e.earning_created, '%c/%e/%Y') AS day,    
                  SUM(e.earning_amount) AS earning_referral
           FROM earnings AS e
           INNER JOIN referrals AS r
           ON r.referral_id = e.earning_referral_id
           WHERE e.earning_account_id = ? 
           AND e.earning_referral_id > 0 
           AND (e.earning_created > DATE_SUB(now(), INTERVAL 90 DAY)) 
           AND r.referral_type = 0
           GROUP BY DATE(e.earning_created)) AS er 
ON (day = er.day)
WHERE earnings.earning_account_id = ?
ORDER BY day

我假设 earning_account_id = ? 是一个问号，因为您用来运行查询的语言在运行查询之前将其替换为实际 ID。