我在这里有一张桌子,我需要显示每种伤害类型的每月统计信息。
事件表:
|incident_id| date |narration|injury_id|
--------------------------------------------
|1 |2015-10-10|long text|1 |
|2 |2015-09-19|long text|2 |
|3 |2015-05-18|long text|3 |
--------------------------------------------
etc..
我在这里有一个看法。
|incident_id| date |narration|injury_id|
--------------------------------------------
|1 |2015-10-10|long text|1 |
|2 |2015-09-19|long text|2 |
|3 |2015-05-18|long text|3 |
--------------------------------------------
etc..
伤害表:
|injury_id |Month|Year|
------------------------
|1 |1 |2015|
|2 |2 |2015|
|3 |2 |2016|
------------------------
事件表中的主键是入射ID,外键是伤害ID。伤害表是事件表所引用的位置。所以我想在html表中显示此输出:
|Month |Jan|Feb|Mar|Apr|May|Jun| Up to Dec
--------------------------------------------
|Homicide |100|200|300|400|500|768|
|Self Accident |354|300|320|380|800|200|
|Vehicular Accident| 20| 40| 50| 90| 16|100|
--------------------------------------------
由于我不知道该怎么办。所以我尝试了这个查询。
DELIMITER //
CREATE PROCEDURE get_stats(d int, y text)
BEGIN
SELECT count(*) AS count FROM incident_view
WHERE injury_id=d AND year=y AND MONTH=1
UNION ALL
SELECT count(*) AS c FROM incident_view
WHERE injury_id=d AND year=y AND MONTH=2
UNION ALL
SELECT count(*) AS count FROM incident_view
WHERE injury_id=d AND year=y AND MONTH=3
UNION ALL
SELECT count(*) AS count FROM incident_view
WHERE injury_id=d AND year=y AND MONTH=4
SELECT count(*) AS count FROM incident_view
WHERE injury_id=d AND year=y AND MONTH=5
UNION ALL
SELECT count(*) AS count FROM incident_view
WHERE injury_id=d AND year=y AND MONTH=6
UNION ALL
SELECT count(*) AS count FROM incident_view
WHERE injury_id=d AND year=y AND MONTH=7
UNION ALL
SELECT count(*) AS count FROM incident_view
WHERE injury_id=d AND year=y AND MONTH=8
UNION ALL
SELECT count(*) AS count FROM incident_view
WHERE injury_id=d AND year=y AND MONTH=9
UNION ALL
SELECT count(*) AS count FROM incident_view
WHERE injury_id=d AND year=y AND MONTH=10
UNION ALL
SELECT count(*) AS count FROM incident_view
WHERE injury_id=d AND year=y AND MONTH=11
UNION ALL
SELECT count(*) AS count FROM incident_view
WHERE injury_id=d AND year=y AND MONTH=12;
END//
DELIMITER ;
只是假设SQL没有错误。所以基本上我必须调用存储过程4次,将每个伤害行的ID传递给存储过程。
我的问题是,是否有更好的方法来实现此输出。
最佳答案
SELECT DATE_FORMAT(date, '%Y-%m') yearmonth
, injury_id
, COUNT(*) total
FROM incident
[WHERE date BETWEEN 'yyyy-mm-dd' AND 'yyyy-mm-dd'] -- optional
GROUP
BY injury_id
, yearmonth;