获取一个聚合行mysql

Question

我有以下数据：

project_type_id  |   total_hours   |  task_completion
-------------------------------------------------------
10               |   5             |  2018-9-10
10               |   4             |  2018-9-11
11               |   10            |  2018-9-10
12               |   2             |  2018-9-10
13               |   9             |  2018-9-10
14               |   8             |  2018-9-11

我试图为给定的total sum of hours输入数据集中的每个可用project_type获取year。

以下是我的查询：

DROP PROCEDURE IF EXISTS getTaskHourReport;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getTaskHourReport`(IN `year` INT)
BEGIN

SET GLOBAL group_concat_max_len=4294967295;
SET @SQL = NULL;
SET @year = year;

    SELECT
    COALESCE(GROUP_CONCAT(DISTINCT
    CONCAT(
    'SUM(CASE WHEN project_type_id = "',project_type_id,'" THEN total_hours ELSE 0 END) AS `',project_type_id,'`'
    )
), '0 as `NoMatchingRows`') INTO @SQL

        FROM `task_details`
        WHERE YEAR(task_completion) = @year;     

    SET @SQL 
    = CONCAT
    (
    '
        SELECT ', @SQL, ' 
        FROM
        task_details
        WHERE YEAR(task_completion) = @year
        GROUP BY project_type_id
    '
    );

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END$$
DELIMITER ;

这个查询提供了以下输出：

10  |  11  |  12  |  13  |  14
-------------------------------
9   |  0   |  0   |  0   |  0
0   |  10  |  0   |  0   |  0
0   |  0   |  2   |  0   |  0
0   |  0   |  0   |  9   |  0
0   |  0   |  0   |  0   |  8

预期产出：

10  |  11  |  12  |  13  |  14
-------------------------------
9   |  10  |  2   |  9   |  8

如何才能像预期的那样得到一行？

Answer 1

删除GROUP BY。所以：

SET @SQL = CONCAT('SELECT ', @SQL, ' ',
                  'FROM task_details '
                  'WHERE YEAR(task_completion) = @year'
                 );

没有GROUP BY的聚合查询将所有行视为单个组。所以，它只返回一行。即使引用的表为空，或者过滤条件移除所有行(在这种情况下，大多数聚合函数返回NULL，但COUNT()返回0)，情况也是如此。