我在这里有一张桌子,我需要显示每种伤害类型的每月统计信息。
事件表:

|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;

07-24 09:37
查看更多