Mysql:使用子字符串值作为列进行查询

Question

我有以下查询，知道如何使用子字符串值作为列来显示输出吗？或者有更好的方法来实现这一目标吗？

select MONTHNAME(from_unixtime(t1.dateline)) AS Month, COUNT(DISTINCT(t1.ticketmaskid)) as Total 

FROM swtickets t1 JOIN swticketauditlogs t2 ON t1.ticketid = t2.ticketid 

WHERE YEAR(from_unixtime(t1.dateline)) = '2014' 

AND t1.totalreplies > 0 AND FIND_IN_SET(t1.departmenttitle, 'Sales') 

AND t2.actionmsg RLIKE 'Email Queue: sales@(abc\.com\.(ph|in)|def.com)' 

GROUP BY Month 

ORDER BY MONTH(from_unixtime(t1.dateline));

输出:

+----------+-------+
| Month    | Total |
+----------+-------+
| January  |   103 |
| February |   102 |
| March    |   124 |
| April    |   105 |
| May      |   123 |
| June     |    12 |
+----------+-------+

我想要以下输出:

+----------+-----------------------------------------------------+
| Month    | sales@abc.com.ph | sales@abc.com.in | sales@def.com |
+----------+------------------+------------------+---------------+
| January  |   10             |     3            |               |
| February |   10             |     4            |               |
| March    |   12             |     4            |               |
| April    |   10             |     56           |               |
| May      |   12             |     5            |               |
| June     |   10             |     5            |               |
+----------+------------------+------------------+---------------+

Answer 1

我设法解决了这个问题，但我认为这不是最好的方法。如果有更好的方法，请告诉我。

SELECT MONTHNAME(M) AS Month, 
COUNT(case when Email = 'sales@abc.com.ph' then Email end) AS 'sales@abc.com.ph', 
COUNT(case when Email = 'sales@abc.com.in' then Email end) AS 'sales@abc.com.in, 
COUNT(case when Email = 'sales@def.com' then Email end) AS 'sales@def.com' 
FROM (SELECT DISTINCT(t1.ticketmaskid), 
from_unixtime(t1.dateline) AS M, 
SUBSTRING_INDEX(t2.actionmsg, 'Email Queue: ', -1) AS Email 
FROM swtickets t1 JOIN swticketauditlogs t2 ON t1.ticketid = t2.ticketid 
WHERE YEAR(from_unixtime(t1.dateline)) = '2014' 
AND t1.totalreplies > 0 
AND FIND_IN_SET(t1.departmenttitle, 'Sales') 
AND t2.actionmsg RLIKE 'Email Queue: sales@(abc\.com\.(ph|in)|def.com)') a 
GROUP BY Month ORDER BY MONTH(M);