我正在努力以所需的格式提取数据,但直到现在仍不成功。
我有下表
id_ticket,date_ticket,office_ticket,status_ticket
我需要查询来返回我,每个月一次,并且总是在同一办公室:
我仅返回具有任何状态的票证总数的查询就是这个。有效!
SELECT
COUNT (id_ticket) as TotalTicketsPerMonth,
'sYear' = YEAR (date_ticket),
'sMonth' = MONTH (date_ticket)
FROM crm_vw_Tickets
WHERE office_ticket = 1
GROUP BY
YEAR (date_ticket), MONTH (date_ticket)
ORDER BY sYear ASC, sMonth ASC
返回状态为5的票证总额
SELECT
COUNT (id_ticket) as TotalTicketsPerMonth,
'sYear' = YEAR (date_ticket),
'sMonth' = MONTH (date_ticket)
FROM crm_vw_Tickets
WHERE office_ticket = 1 AND status_ticket = 5
GROUP BY
YEAR (date_ticket), MONTH (date_ticket)
ORDER BY sYear ASC, sMonth ASC
但我需要返回的内容如下:
Year Month Total Status5 Status6
2018 1 15 5 3
2018 2 14 4 5
2018 3 19 2 8
感谢您的帮助。
你近了 您可以使用CASE
表达式来获取所需的内容:
SELECT
COUNT (id_ticket) as TotalTicketsPerMonth,
SUM(CASE WHEN status_ticket = 5 THEN 1 END) as Status5,
SUM(CASE WHEN status_ticket = 6 THEN 1 END) as Status6,
'sYear' = YEAR (date_ticket),
'sMonth' = MONTH (date_ticket)
FROM crm_vw_Tickets
WHERE office_ticket = 1
GROUP BY YEAR (date_ticket), MONTH (date_ticket)
ORDER BY sYear ASC, sMonth ASC
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句