想求在某段时间内,增删改,各发生过几次
SELECT ip, time,
sum( IF ( content LIKE '%修改%', count( content ) , 0 ) ) AS con_mod,
sum( IF ( content LIKE '%删除%', count( content ) , 0 ) ) AS con_del,
sum( IF ( content LIKE '%增加用户%', count( content ) , 0 ) ) AS con_add
FROM log
GROUP BY date_format( time, '%Y-%m' )
------------------------------
结果:
时间, 类型, 次数, 类型,次数, 类型, 次数
2012-01, 修改, 5次, 删除,2次, 增加, 3次
2012-02, 修改, 2次, 删除,8次, 增加, 12次
[解决办法]
SELECT date_format( time, '%Y-%m' ) ,
sum( IF ( content LIKE '%修改%', 1 , 0 ) ) AS 修改,
sum( IF ( content LIKE '%删除%', 1 , 0 ) ) AS 删除,
sum( IF ( content LIKE '%增加用户%', 1 , 0 ) ) AS 增加
FROM log
GROUP BY date_format( time, '%Y-%m' )
