按日期+其他字段求和并组合多个字段 [英] Sum and group multiple fields by date + other field

问题描述

我有一张3列的表格，如下所示。我想总结每种类型（仅0/1/2）的成本，然后按月和年（DATE字段）对它们进行分组，全部使用select语句。

  + ------------ + ------ + ------- + 
 | DATE | TYPE |成本| 
 + ------------ + ------ + ------- + 
 | Feb-1-2003 | 0 | 19.40 | 
 | Feb-5-2010 | 1 | 28.10 | 
 | Mar-3-2011 | 2 | 64.20 | 
 | Sep-8-2012 | 0 | 22.60 | 
 | Dec-6-2013 | 1 | 13.50 | 
 + ------------ + ------ + ------- + 
  

输出结果如下：

  + ------- --- + ------------ + ------------ + ------------ + 
 | PERIOD | TYPE0 | TYPE1 | TYPE2 | 
 + ---------- + ------------ + ------------ + -------- ---- + 
 | 2003年1月| 123123.12 | 23432.12 | 9873245.12 | 
 | 2003年2月| 123123.12 | 23432.12 | 9873245.12 | 
 | 2003年3月| 123123.12 | 23432.12 | 9873245.12 | 
等... 
 + ---------- + ------------ + ------------ + ------------ + 
  

有人可以帮助选择这条语句吗？

解决方案

Mysql不是为透视查询而设计的。然而，这并不是说这是不可能的：

$ p $ SELECT
CONCAT（ （类型= 0，成本，0））作为类型0，
SUM（IF（类型= 1，成本， 0））AS TYPE1，
SUM（IF（TYPE = 2，COST，0））AS TYPE2
FROM
t1
GROUP BY
PERIOD


I have a table with 3 columns as shown below. I would like to summarize the cost for each TYPE (only 0/1/2), and then group it by Month and Year (of the DATE field), all using a select statement.

+------------+------+-------+
| DATE       | TYPE | COST  |
+------------+------+-------+
| Feb-1-2003 |  0   | 19.40 |
| Feb-5-2010 |  1   | 28.10 |
| Mar-3-2011 |  2   | 64.20 |
| Sep-8-2012 |  0   | 22.60 |
| Dec-6-2013 |  1   | 13.50 |
+------------+------+-------+

The output would look like:

+----------+------------+------------+------------+
| PERIOD   | TYPE0      | TYPE1      | TYPE2      |
+----------+------------+------------+------------+
| Jan 2003 | 123123.12  | 23432.12   | 9873245.12 |
| Feb 2003 | 123123.12  | 23432.12   | 9873245.12 |
| Mar 2003 | 123123.12  | 23432.12   | 9873245.12 |
etc...
+----------+------------+------------+------------+

Can someone help with this select statement?

解决方案

Mysql is not designed for pivot queries. It's better to aggregate in some other language.. That's not to say it's impossible, though:

SELECT
    CONCAT(MONTH(DATE), ' ', YEAR(DATE)) AS PERIOD,
    SUM(IF(TYPE = 0, COST, 0)) AS TYPE0,
    SUM(IF(TYPE = 1, COST, 0)) AS TYPE1,
    SUM(IF(TYPE = 2, COST, 0)) AS TYPE2
FROM
    t1
GROUP BY
    PERIOD

这篇关于按日期+其他字段求和并组合多个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！