php - MySQL 查询按月返回行数

Question

我正在尝试获取counts number of rows的在 table最后12 months ，按月给予12 counts .

到目前为止，我的做法非常丑陋:

SELECT (
    SELECT COUNT(i.id) FROM intrusions i, devices d WHERE i.device_id = d.id AND d.primary_owner_id = '1' AND FROM_UNIXTIME(start_time/1000) like '2014-04%',
    SELECT COUNT(i.id) FROM intrusions i, devices d WHERE i.device_id = d.id AND d.primary_owner_id = '1' AND FROM_UNIXTIME(start_time/1000) like '2014-03%',
    SELECT COUNT(i.id) FROM intrusions i, devices d WHERE i.device_id = d.id AND d.primary_owner_id = '1' AND FROM_UNIXTIME(start_time/1000) like '2014-02%',
    etc
)  

表格的设置如下 1 user可以有many devices和1 device可以有many intrusions ，因此有额外的条件。

primary_owner_id和 date将动态添加在PHP中使用prepared statements 。有没有更好的方法来写出来，而不需要太多的重复和绑定(bind) 24 个参数？任何帮助将不胜感激

Answer 1

您应该使用分组来获得此结果。所以像这样。

SELECT
    CONCAT(YEAR(FROM_UNIXTIME(start_time/1000)), '-', MONTH(FROM_UNIXTIME(start_time/1000))) AS `year_month`,
    COUNT(id) AS `count`
FROM intrusions AS i
INNER JOIN devices AS d
  ON i.device_id = d.id
WHERE d.primary_owner = ?
GROUP BY `year_month`
ORDER BY `year_month` DESC
LIMIT 12