每年創建和解決（或合併）的工單

在 MySQL 5.5 上執行的第三方應用程序 (RT4) 中，我有一系列服務台票據，表示為表中的記錄。每條記錄都有一個創建的日期/時間、一個解析的日期/時間和一個合併標誌（NULL或1）。

id int(11) AI PK, Created datetime, Resolved datetime, IsMerged smallint(6)

創建工單時，已填充已創建欄位，已解決欄位和合併標誌為NULL. 當工單關閉時，將填充已解決的欄位。如果票證是重複的，則合併標誌設置為1，並且解決日期設置為1970-01-01 00:00:00。

我查看了一些提供的相關答案和那些mysql：計算每天開放的票數似乎是最有希望的。我覺得這幾乎是我需要的，但我無法從那裡的答案推斷出我的案例。

我可以很容易地列出每年創建的門票數量 -

select year(Created) as Created_Yr, count(*) as N_Tickets from rt4.Tickets
group by year(Created)
order by 1

一個類似的查詢可以列出每年的非合併票的集合——

select year(Resolved) as Resolved_Yr, count(*) as N_Tickets from rt4.Tickets
where Resolved is not null
group by year(Resolved)
order by 1

但是，我想要列出每年解決或合併的票證數量。

我可以像這樣年復一年地做——

select 2016 as Resolved_Yr, count(*) as N_Tickets from rt4.Tickets
where ( Resolved >= '2016-01-01' and Resolved < '2017-01-01' )
or ( Created >= '2016-01-01' and Created < '2017-01-01' and IsMerged = 1 )

我們有 2012 年到 2017 年的數據，我希望避免每年執行一次查詢並手動整理結果。

是否有一個查詢會列出數據庫中存在的所有年份的已解決年份（或創建年份，如果票證已合併）？

SELECT * 
 FROM ( SELECT DISTINCT year(Created) AS Created
          FROM rt4.Tickets
      ) AS w

 LEFT JOIN ( SELECT year(Resolved) AS Resolved
                  , count(*) AS resolved_cnt
               FROM rt4.Tickets
              WHERE Resolved IS NOT NULL
              GROUP BY year(Resolved) 
           ) AS z
        ON z.Resolved = w.Created

 LEFT JOIN ( SELECT year(Created) AS Created
                  , count(*) AS merged_cnt
               FROM rt4.Tickets
              WHERE Merged IS NOT NULL
              GROUP BY year(Created) 
           ) AS q
        ON q.Created = w.Created
;

當然，您必須記住，像, or子句year()中的功能限制不能通過索引加速，並且可能非常慢。WHERE``JOIN...ON``GROUP BY

我希望不會定期執行該查詢。

引用自：https://dba.stackexchange.com/questions/180104