结合来自同一个表的2个MYSQL计数查询

Question

我想创建一个查询，该查询会给我打开的票数＃&按指定的时间周期分组。有没有人有关于我如何完成这一任务的想法？

如果我从T1.closed更改时间参考的而不是T1.opened我得到正确的分组为一列，而不是其他。

以下是我到目前为止。

SELECT 
FROM_DAYS(TO_DAYS(T1.created) -MOD(TO_DAYS(T1.created) -1, 7)) as week, 
COUNT(T1.created) as Opened, COUNT(T2.ticket) as Closed 

FROM issues as T1 
LEFT JOIN issues T2 on T1.ticket = T2.ticket AND T1.closed 
BETWEEN (DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) 
AND (DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND T1.closed IS NOT NULL 

WHERE 
T1.created > (DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) 
AND 
T1.created < (DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) 

GROUP BY 
week 

Answer 1

据我了解t1.opened和t1.closed的日期，这样你就可以去：

select `week`, sum(`opened`), sum(`closed`) from 
(
    select week(t1.opened) as `week`, count(*) as `opened`, 0 as `closed` 
    from issues as t1 
    where t1.opened is not null AND ##FILTER BY OPENED## 
    group by week(t1.opened) 
union all 
    select week(t2.closed) as `week`, 0 as `opened`, count(*) as `closed` 
    from issues as t2 
    where t2.closed is not null AND ##FILTER BY CLOSED## 
    group by week(t2.closed) 
) as t3 
group by `week` 

记：你看到有2个查询，不加入他们的行列，因为可能有几周没有关闭/打开问题

注意2：如果您想按创建日期添加过滤器 - 您需要考虑应该如何工作，例如问题创建于1月1日，1月8日开放，1月8日关闭，1月15日关闭，应该产生以下每个范围：1-7,2-8,9-16？