mysql - 按多个时间戳分组；连接月份但保留单独的值

Question

我需要按月获取已结算和未结算订单的数量。这是我到目前为止所拥有的:

SELECT 
    CONCAT(MONTH(time_settle), "/", YEAR(time_settle)) as month_settled, count(*) as settled 
FROM payment_order 
WHERE time_settle IS NOT NULL 
AND datediff(time_settle,NOW())>-90 
AND datediff(time_settle,NOW())<+90 
AND account_id=28 AND current_state !="deleted" 
GROUP BY MONTH(time_settle);

结果是:

month_settled | settled
-------------------------
6/2014        |   1
9/2014        |   10
12/2014       |   1

不确定我使用time_due:

SELECT 
    CONCAT(MONTH(time_due), "/", YEAR(time_due)) as month_due, count(*) as unsettled 
FROM payment_order 
WHERE time_settle IS NULL 
AND datediff(time_due,NOW())>-90 
AND datediff(time_due,NOW())<+90 
AND account_id=28 
AND current_state !="deleted" 
GROUP BY MONTH(time_due);

结果是:

month_due     |  unsettled
-------------------------
8/2014        |   1
9/2014        |   8
10/2014       |   2

我需要一个看起来像这样的结果:

month         |  settled |  unsettled | total
----------------------------------------------
6/2014        |    1     |     0      |   1
8/2014        |    0     |     1      |   1
9/2014        |    10    |     8      |   18
10/2014       |    0     |     2      |   2
12/2014       |    1     |     0      |   1

有什么想法吗？如果我的总和标准是两个不同的月份，我该如何按月进行分组？我需要已结算的交易按 time_settle 分组，未结算的交易按 time_due 分组。

Answer 1

好的，试试这个

SELECT t.month, 
    coalesce(t1.settled, 0) as settled, 
    coalesce(t2.unsettled, 0) as unsettled,
    coalesce(t1.settled, 0) + coalesce(t2.unsettled, 0) as total
FROM
(   SELECT CONCAT(MONTH(a.t_month), "/", YEAR(a.t_month)) as month
    FROM
    (   SELECT COALESCE(time_settle, time_due) as t_month 
        FROM payment_order
    )a
    WHERE datediff(a.t_month,NOW())>-90 AND datediff(a.t_month,NOW())<+90
    GROUP BY month
)t
LEFT JOIN(SELECT CONCAT(MONTH(time_settle), "/", YEAR(time_settle)) as month_settled, count(*) as settled FROM payment_order WHERE time_settle IS NOT NULL AND datediff(time_settle,NOW())>-90 AND  datediff(time_settle,NOW())<+90 AND account_id=28 AND current_state !="deleted" GROUP BY EXTRACT(YEAR_MONTH FROM time_settle)) t1 
    on t1.month_settled = t.month
LEFT JOIN(SELECT CONCAT(MONTH(time_due), "/", YEAR(time_due)) as month_due, count(*) as unsettled FROM payment_order WHERE time_settle IS NULL AND account_id=28 AND current_state !="deleted" GROUP BY EXTRACT(YEAR_MONTH FROM time_due)) t2 
    on t2.month_due = t.month;

DEMO