sqlite - 将两个sql查询合并为一个分组

Question

所以我在底部有以下两个查询。现在我怎么能把它们组合在一张 table 上呢？也是这样:

+-------+-----------------+----------------+
| Date  | Tickets created | Tickets closed |
+-------+-----------------+----------------+
| 10/25 |              50 |             45 |
| 10/26 |              40 |             40 |
| 10/27 |              30 |             30 |
| 10/28 |              10 |              0 |
+-------+-----------------+----------------+

门票表如下所示:

+----+----------------------+----------------------+------------------+
| id |      created_at      |      closed_at       | master_ticket_id |
+----+----------------------+----------------------+------------------+
|  1 | 2013-10-12 00:00:00' | 2013-10-12 06:00:00' |                  |
|  2 | 2013-11-11 00:00:00' |                      |                  |
|  3 | 2013-11-12 00:00:00' |                      |                2 |
|  4 | 2013-11-13 10:00:00' | 2013-11-13 12:00:00' |                  |
|  5 | 2013-12-12 00:00:00' | 2013-12-12 07:00:00' |                  |
+----+----------------------+----------------------+------------------+

查询 #1

select strftime("%m-%d",tickets.created_at) as 'Date',
count(tickets.created_at)as 'Tickets created'
from tickets 
WHERE tickets.created_at >= '2013-10-01 00:00:00' 
and tickets.created_at< '2013-10-31 00:00:00'
and tickets.master_ticket_id is Null
Group by strftime("%m-%d",tickets.created_at) 

产生:

+-------+-----------------+
| Date  | Tickets created |
+-------+-----------------+
| 10/25 |              50 |
| 10/26 |              40 |
| 10/27 |              30 |
+-------+-----------------+

查询 #2

select strftime("%m-%d",tickets.closed_at) as 'Date',
count(tickets.closed_at)as 'Tickets closed'
from tickets 
WHERE tickets.closed_at >= '2013-10-01 00:00:00' 
and tickets.closed_at< '2013-10-31 00:00:00'
and tickets.master_ticket_id is Null
Group by strftime("%m-%d",tickets.closed_at) 

产生:

+-------+----------------+
| Date  | Tickets closed |
+-------+----------------+
| 10/25 |             50 |
| 10/26 |             40 |
| 10/27 |             30 |
+-------+----------------+

Answer 1

[因上一版本存在一些问题而编辑]

似乎更难避免 UNION比我希望的要好，但据我测试，以下应该有效。

SELECT Date, SUM(created) AS 'Tickets created', SUM(closed) AS 'Tickets closed' FROM
(
    SELECT strftime("%m-%d",tickets.created_at) AS 'Date',
    COUNT(tickets.created_at) AS 'created', 
    0 AS 'closed'
    FROM tickets 
    WHERE tickets.created_at >= '2013-10-01 00:00:00' 
    AND tickets.created_at< '2013-10-31 00:00:00'
    AND tickets.master_ticket_id IS NULL
    GROUP BY Date

UNION ALL

    SELECT strftime("%m-%d",tickets.closed_at) AS 'Date',
    0 AS 'created',  
    COUNT(tickets.closed_at) AS 'closed'
    FROM tickets 
    WHERE tickets.closed_at >= '2013-10-01 00:00:00' 
    AND tickets.closed_at< '2013-10-31 00:00:00'
    AND tickets.master_ticket_id IS NULL
    GROUP BY Date
)
GROUP BY Date