使用 sum(case . . ) :

select extract(hour from time), 
       sum(case when type = 'dog' then count else 0 end) as dog,
       sum(case when type = 'cat' then count else 0 end) as cat,
       sum(case when type = 'bird' then count else 0 end) as bird
from t
group by extract(hour from time);

对于这个版本，您需要显式地列出每个值。如果需要动态sql，则需要将查询构造为字符串，然后运行它。

我还没有尝试过这个方法，但我认为一种可行的方法是对不同的值进行交互，并根据gordon linoff的答案生成一个查询：

# Get array of distinct types

DECLARE myTypes ARRAY<STRING>;
SET myTypes = ( select distinct(`TYPE`) form `table`);

# get size of array

DECLARE x INT64;
SET x = ARRAY_LENGTH(myTypes);

# start making query

DECLARE myQuery STRING;
SET myQuery = "select extract(hour from time),";

LOOP

    x = x-1;

    # Add column for each type
    SET myQuery = CONCAT(myQuery,format("sum(case when type = '%s' then count else 0 end) as %s,",myTypes[OFFSET(x)]);

    IF x == 0 THEN LEAVE;
    END IF;
END LOOP;

# finish up query

SET myQuery = CONCAT(myQuery,"from table group by extract(hour from time)")

EXECUTE IMMEDIATE myQuery;