MySQL查询返回多个求和列和分组列

Part 2 to the question asked here -

这里问的第2部分 -

MySQL query to return multiple summed columns

MySQL查询返回多个求和列

I have the following data in my SQL table. Imagine that there are many more rows spanning years worth of data.

我的SQL表中有以下数据。想象一下,还有更多行跨越多年的数据。

uid    user    category    count    date
--------------------------------------------------
1      henry   RED         4        2013-04-01
2      john    BLUE        3        2013-04-11
4      eric    GREEN       2        2013-04-22
3      alice   RED         5        2013-05-01
5      eric    BLUE        2        2013-05-12
6      john    RED         3        2013-05-23
7      henry   GREEN       2        2013-06-03
8      eric    RED         3        2013-06-08
9      john    BLUE        5        2013-07-11
... (more) ...

I would like a query that gives me back the following data, grouped in columns according to the date. I know how to use the GROUP BY command to group data into rows. But I am not sure how to do it by columns.

我想要一个查询,它会返回以下数据,根据日期分组。我知道如何使用GROUP BY命令将数据分组到行中。但我不知道如何通过专栏来做到这一点。

category    May 2013     April 2013  ... (more)
------------------------------------
RED         8            4           
BLUE        2            3     
GREEN       0            2

I realize there are probably a lot of options, as far as using date and time functions. I'm just looking more for the basic structure of how to set up the logic for SQL to give me an arbitrary number of columns.

我意识到,就使用日期和时间函数而言,可能有很多选项。我只是想了解如何为SQL设置逻辑给我一个任意数量的列的基本结构。

3 个解决方案

#1

You should try summing by cases (and sum 0 when not true). Like the following:

你应该尝试按案例求和(当不是真时加0)。如下:

SELECT 
   ...
   SUM(CASE WHEN date BETWEEN ... AND ... THEN 'count' ELSE 0 END) as 'May 2013',
   SUM(CASE WHEN date BETWEEN ... AND ... THEN 'count' ELSE 0 END) as 'April 2013',
   etc.
FROM ...,
GROUP BY category

#2

I think you must use Procedural Language My SQL to looping the month/year.

我认为你必须使用过程语言我的SQL来循环月/年。

#3

Try something like this:

尝试这样的事情:

SELECT 
  category,
  SUM(CASE WHEN date BETWEEN '2013-05-01' AND '2013-06-01' THEN count ELSE 0 END) AS 'May 2013',
  SUM(CASE WHEN date BETWEEN '2013-04-01' AND '2013-05-01' THEN count ELSE 0 END) AS 'April 2013',
  ... (more)   
FROM dbtable
GROUP BY category

#1