mysql - mysql单表组通过删除零元素

我有一张桌子，如下

| ID |        CREATED       |                TITLE           |
| 1  |  07/09/2015 14:02:48 |  Render farm problem           |
| 2  |  06/16/2015 09:34:20 | Render server THSSE12 crashing |
| 3  |  05/16/2015 09:44:38 |  Patch to LG4 switch port 25   |

我希望能够计算出标题字段中关键字的出现次数，例如按年份和月份呈现和格式化结果

| YEAR | MONTH | COUNT |
|2015  | 5     | 0     |
|2015  | 6     | 1     |
|2015  | 7     | 1     |

我试过几个内心的连接，但没有任何喜悦，因为0个月的计数不显示。我就在这里：-

 SELECT
    CONCAT(YEAR(c.CREATED),MONTH(c.CREATED)) AS cdate,
    c.CREATED,
    COUNT(c.ID)
FROM
    HD_TICKET AS c
LEFT JOIN
    (SELECT
        CONCAT(YEAR(t.CREATED),MONTH(t.CREATED)) AS sdate,
        t.CREATED,
        COUNT(t.ID) AS tid
        FROM HD_TICKET t
        WHERE t.TITLE LIKE '%render%'
    ) AS t_count
ON c.CREATED = t_count.CREATED
GROUP BY YEAR(c.CREATED), MONTH(c.CREATED)

我非常感谢你的帮助！

最佳答案

这首先生成所有年/月值，然后左键联接所需的数据。内联所有详细信息。如果你愿意的话，你可以在几年内使用同样的伎俩。fiddle here

select  calendar.year_, calendar.month_,
        coalesce(mydata.count_,0) as count_                    -- coalesce used to obtain 0 on missing data.
from
(   select y.year_, m.month_
    from
    (select distinct YEAR(CREATED) as year_ FROM hd_ticket) as y      -- all posible years
         ,                                                            -- comma here produces cross join
    (select 1 as month_ union all select 2                            -- all months
     union all select 3 union all select 4
     union all select 5 union all select 6
     union all select 7 union all select 8
     union all select 9 union all select 10
     union all select 11 union all select 12) as m
) as calendar                                                  -- a calendar: all years and all months
left join
(
    select count(*) as count_, year(CREATED) as year_, month(CREATED) as month_
    from HD_TICKET
    where TITLE like '%render%'
    group by year(CREATED), month(CREATED)
) as mydata
on calendar.year_ = mydata.year_ and calendar.month_ = mydata.month_
order by 1, 2

关于mysql - mysql单表组通过删除零元素，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39721961/