mysql：計算每天開放的門票數量

我有一個tabName包含以下行的表：

id(PK) .. Created(datetime)     Resolved(datetime) Status(new/resolved/open)
1          2015-05-10 00:00:00   1970-01-01 ..       open
2          2015-05-10 ..         1970-01-01 ..       new
3          2015-05-10            2015-05-12          resolved
4          2015-05-11 ..         1970-01-01 ..       open
5          2015-05-11 ..         1970-01-01 ..       new
6          2015-05-11            2015-05-11          resolved
7          2015-05-12 ..         1970-01-01 ..       open
8          2015-05-12 ..         1970-01-01 ..       new
9          2015-05-12            2015-05-12          resolved

注意： 1970-01-01 .. 是所有仍然開放的門票的預設時間。

我必須建構一個查詢以獲取仍然開放的門票數量，日期明智。比如說我在 5 月 10 日開了 3 張票。請注意，對於id = 3，解決日期不是 10，而是在 5 月 12 日關閉。

我提出了一個類似的查詢

SELECT DATE_FORMAT(Created, '%d-%m') AS createdd, COUNT(1)
FROM tabName
WHERE DATE_FORMAT(Resolved, '%d-%m') > DATE_FORMAT(Created, '%d-%m')
     AND YEAR(Created) = '2015'
GROUP BY createdd;

上面的查詢獲取所有Resolved日期大於考慮計數的日期的票。這也可能計算較新的門票。此外，如果尚未解決的舊票不計算在內。

有沒有其他方法來建構所需的查詢？從過去 3 天開始，我一直在處理這個查詢。還是查不出來。

更新：正如 Abhik 所建議的，我在 sqlfiddle 站點中嘗試過。

這是我創建的：

CREATE TABLE test (id int,created resolve id datetime,status ) VALUES (1,'2015-05-10 00:00:00','1970-01-01 00:00:00','open'),datetime, status varchar(15), PRIMARY KEY (id)); INSERT INTO test (``,‘created’,‘resolved’,`

 (2,'2015-05-10 00:00:00','1970-01-01 00:00:00','new'),

 (3,'2015-05-10 00:00:00','2015-05-12 00:00:00','resolved'),

 (4,'2015-05-11 00:00:00','1970-01-01 00:00:00','open'),

 (5,'2015-05-11 00:00:00','1970-01-01 00:00:00','new'),

 (6,'2015-05-11 00:00:00','2015-05-11 00:00:00','resolved'),

 (7,'2015-05-12 00:00:00','1970-01-01 00:00:00','open'),

 (8,'2015-05-12 00:00:00','1970-01-01 00:00:00','new'),

 (9,'2015-05-12 00:00:00','1970-01-01 00:00:00','open')

 ;
 `

當我跑步時

` select created_date, @open:= @open+open_ticket-resolved as open from(select date_format(created,’%d-%m’) as created_date ,

總和（當狀態=‘打開’然後 1 否則 0 結束時的情況）+

總和（當狀態=‘新’然後 1 否則 0 結束時的情況）+

sum( status=‘resolved’ and ( date(resolved) > date(created) 或

date(resolved) = date(created)) then 1 else 0 end) as open_ticket,

總和（狀態 = ‘resolved’ 和 date(resolved) = date(created) then 1 的情況

else 0 end) 已解決

從測試

在哪裡

年份（創建）=‘2015’

按 created_date 分組）x，

(select @open:=0)r order by created_date ; `

我得到以下輸出

created_date open 10-05 3 11-05 5 12-05 8

此處 12-05 的未處理票數必須為 7。上述查詢包括票號為 “3” 的票證，該票證於 12-05 關閉。那麼有什麼辦法可以排除呢？如果我能夠做到這一點，那麼我將擁有完整的查詢。

您可以為此計算使用條件總和，例如

select 
date_format(created,'%d-%m') as created_date , 
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

這是一個測試案例

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select 
   -> date_format(created,'%d-%m') as created_date , 
   -> sum( case when status='open' then 1 else 0 end )+
   -> sum( case when status='new' then 1 else 0 end )+
   -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
   -> from test
   -> where
   -> YEAR(Created) = '2015'
   -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

更新 根據評論，打開的計數是增量的。所以下面應該做的伎倆

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
select 
date_format(created,'%d-%m') as created_date , 
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
from test
where
YEAR(Created) = '2015'
group by created_date
)x,(select @open:=0)r order by created_date ; 

從上面的範例數據中，它將具有

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

更新：

計算在給定日期解決的以前的票，並從當天的開放票中扣除，因為你需要一個left join東西

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
select 
date_format(t.created,'%d-%m') as created_date , 
sum( case when t.status='open' then 1 else 0 end )+
sum( case when t.status='new' then 1 else 0 end )+
sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
coalesce(tot,0) as resolved_ticket
from test t left join(
   select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
)x on date(x.resolved) = date(t.created) 
where
YEAR(t.created) = '2015'
group by created_date
)x,(select @open:=0)r order by created_date ; 

輸出 ：

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+

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