javascript - 如何在 mysql 中将多个结果连接成几个通用结果？

Question

我打算画一个条形图。 x 轴包含标签。标签是从 mysql 请求生成的。标签也太多而无法放入页面图表中。我打算将一些熟悉的标签合二为一。我相信 existign 设置很好，但是我想编辑我的 mysql 查询以解决这个问题。

清除问题的示例。

这是我当前的 mysql 查询

$query = "SELECT DISTINCT HITYPE,
            SUM(IF(STATUS = 'OPEN',1,0)) AS OPEN,
            SUM(IF(STATUS = 'CLOSED',1,0)) AS CLOSED,
            COUNT(STATUS) AS 'TOTAL'
            FROM hazardreport WHERE HITYPE IS NOT NULL AND RISKLEVEL='$risklevel' AND DATE(DREPORTED) BETWEEN '$dateone' AND 
            '$datetwo'
            GROUP BY HITYPE
            ORDER BY TOTAL DESC";

生成的数据为

item1, 1, 0, 1
item1.1 , 3 , 2 , 5
item2 , 1 , 1 , 2
item2.2, 5, 1 , 6

我想编辑查询，以便将生成的数据合并为:

item 1, 4, 2 , 6
item 2, 6, 2, 8

我希望这是有道理的。

Answer 1

尝试按 hitype 字符串值的第一部分对结果进行分组。

SELECT HITYPE,
SUM(IF(STATUS = 'OPEN',1,0)) AS OPEN,
SUM(IF(STATUS = 'CLOSED',1,0)) AS CLOSED,
COUNT(STATUS) AS TOTAL
from (
    select SUBSTRING_INDEX(HITYPE, '.', 1) as HITYPE, status
    FROM hazardreport WHERE HITYPE IS NOT NULL AND RISKLEVEL='$risklevel'
    AND DATE(DREPORTED) BETWEEN '$dateone' AND '$datetwo'
) as results
GROUP BY HITYPE
ORDER BY TOTAL DESC