mysql求和语句月份,sql – 按月求和并将月份作为列

tablefunc模块

我会用crosstab()来做这件事.如果您还没有安装附加模块tablefunc：

CREATE EXTENSION tablefunc

建立

CREATE TEMP TABLE tbl

(id int, extra_info varchar(3), month date, value int);

INSERT INTO tbl (id, extra_info, month, value)

VALUES

(1, 'abc', '2012-01-01', 10),

(1, 'abc', '2012-02-01', 20),

(2, 'def', '2012-01-01', 10),

(2, 'def', '2012-02-01', 5),

(1, 'abc', '2012-01-01', 15),

(3, 'ghi', '2012-03-01', 15);

我在基表中使用实际日期,因为我假设只是为了简化你的问题而隐藏它.但只有月份名称,ORDER BY就没有了.

询问

SELECT * FROM crosstab(

$$SELECT id, extra_info, to_char(month, 'mon'), sum(value) AS value

FROM tbl

GROUP BY 1,2,month

ORDER BY 1,2,month$$

,$$VALUES

('jan'::text), ('feb'), ('mar'), ('apr'), ('may'), ('jun')

, ('jul'), ('aug'), ('sep'), ('oct'), ('nov'), ('dec')$$

)

AS ct (id int, extra text

, jan int, feb int, mar int, apr int, may int, jun int

, jul int, aug int, sep int, oct int, nov int, dec int);

结果：

id | extra | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec

----+-------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----

1 | abc | 25 | 20 | | | | | | | | | |

2 | def | 10 | 5 | | | | | | | | | |

3 | ghi | | | 15 | | | | | | | | |

安装tablefunc模块需要一些开销和一些学习,但结果查询更快,更短,更通用.